Report problem in 2007

  • Thread starter Secret Squirrel
  • Start date
S

Secret Squirrel

I have a database that I use with version 2003 and also now with 2007. When I
run this report in 2003 it works fine but when I open it in 2007 I get an
error "Syntax error in PARAMETER clause". Why would I get this error in 2007
but when I open it in 2003 it works fine? Here is the SQL for the report:

PARAMETERS [Forms]![frmReportSelect]![cboMonth] Text ( 255 ),
[Forms]![frmReportSelect]![cboYear] Long;
SELECT tblLateDeliveries.FSONO, tblCustomers.FCOMPANY,
tblLateDeliveries.FORDERDATE, tblLateDeliveries.FPARTNO,
tblLateDeliveries.FORDERQTY, tblLateDeliveries.FINUMBER,
tblLateDeliveries.FDUEDATE, tblLateDeliveries.FLSHIPDATE,
DateDiff("d",[FDUEDATE],[FLSHIPDATE])-(DateDiff("ww",[FDUEDATE],[FLSHIPDATE],7)+DateDiff("ww",[FDUEDATE],[FLSHIPDATE],1))
AS DaysLate,
DateDiff("d",[FORDERDATE],[FLSHIPDATE])-(DateDiff("ww",[FORDERDATE],[FLSHIPDATE],7)+DateDiff("ww",[FORDERDATE],[FLSHIPDATE],1))
AS LeadTime, tblDepartments.Department, tblType.Type,
Format([FLSHIPDATE],"mmmm") AS MonthFormat, Format([FLSHIPDATE],"yyyy") AS
YearFormat, Forms!frmReportSelect!cboDept AS Expr1, tblEmployees.Initials,
tblEmployees_1.Initials,
IIf(IsNull(tblEmployees.Initials)=True,"",tblEmployees.Initials & " / " &
tblEmployees_1.Initials) AS Expr3
FROM tblEmployees AS tblEmployees_1 RIGHT JOIN (tblType RIGHT JOIN
(tblCustomers RIGHT JOIN (tblDepartments RIGHT JOIN (tblEmployees RIGHT JOIN
tblLateDeliveries ON tblEmployees.ID = tblLateDeliveries.RESP1) ON
tblDepartments.DeptID = tblLateDeliveries.DEPARTMENT) ON
tblCustomers.CUSTOMERID = tblLateDeliveries.FCOMPANY) ON tblType.TypeID =
tblLateDeliveries.TYPE) ON tblEmployees_1.ID = tblLateDeliveries.RESP2
WHERE (((tblDepartments.Department)=[Forms]![frmReportSelect]![cboDept]) AND
((Format([FLSHIPDATE],"mmmm"))=[Forms]![frmReportSelect]![cboMonth]) AND
((Format([FLSHIPDATE],"yyyy"))=[Forms]![frmReportSelect]![cboYear])) OR
(((Format([FLSHIPDATE],"mmmm"))=[Forms]![frmReportSelect]![cboMonth]) AND
((Format([FLSHIPDATE],"yyyy"))=[Forms]![frmReportSelect]![cboYear]) AND
(([Forms]![frmReportSelect]![cboDept]) Is Null));
 
T

Tom van Stiphout

On Thu, 8 Nov 2007 09:41:03 -0800, Secret Squirrel

I would take those two parameters out, and try again.
-Tom.
 
S

Secret Squirrel

If I take the parameters out then the report won't work. Is there something
that has changed in 2007 with regards to the parameters?

Tom van Stiphout said:
On Thu, 8 Nov 2007 09:41:03 -0800, Secret Squirrel

I would take those two parameters out, and try again.
-Tom.

I have a database that I use with version 2003 and also now with 2007. When I
run this report in 2003 it works fine but when I open it in 2007 I get an
error "Syntax error in PARAMETER clause". Why would I get this error in 2007
but when I open it in 2003 it works fine? Here is the SQL for the report:

PARAMETERS [Forms]![frmReportSelect]![cboMonth] Text ( 255 ),
[Forms]![frmReportSelect]![cboYear] Long;
SELECT tblLateDeliveries.FSONO, tblCustomers.FCOMPANY,
tblLateDeliveries.FORDERDATE, tblLateDeliveries.FPARTNO,
tblLateDeliveries.FORDERQTY, tblLateDeliveries.FINUMBER,
tblLateDeliveries.FDUEDATE, tblLateDeliveries.FLSHIPDATE,
DateDiff("d",[FDUEDATE],[FLSHIPDATE])-(DateDiff("ww",[FDUEDATE],[FLSHIPDATE],7)+DateDiff("ww",[FDUEDATE],[FLSHIPDATE],1))
AS DaysLate,
DateDiff("d",[FORDERDATE],[FLSHIPDATE])-(DateDiff("ww",[FORDERDATE],[FLSHIPDATE],7)+DateDiff("ww",[FORDERDATE],[FLSHIPDATE],1))
AS LeadTime, tblDepartments.Department, tblType.Type,
Format([FLSHIPDATE],"mmmm") AS MonthFormat, Format([FLSHIPDATE],"yyyy") AS
YearFormat, Forms!frmReportSelect!cboDept AS Expr1, tblEmployees.Initials,
tblEmployees_1.Initials,
IIf(IsNull(tblEmployees.Initials)=True,"",tblEmployees.Initials & " / " &
tblEmployees_1.Initials) AS Expr3
FROM tblEmployees AS tblEmployees_1 RIGHT JOIN (tblType RIGHT JOIN
(tblCustomers RIGHT JOIN (tblDepartments RIGHT JOIN (tblEmployees RIGHT JOIN
tblLateDeliveries ON tblEmployees.ID = tblLateDeliveries.RESP1) ON
tblDepartments.DeptID = tblLateDeliveries.DEPARTMENT) ON
tblCustomers.CUSTOMERID = tblLateDeliveries.FCOMPANY) ON tblType.TypeID =
tblLateDeliveries.TYPE) ON tblEmployees_1.ID = tblLateDeliveries.RESP2
WHERE (((tblDepartments.Department)=[Forms]![frmReportSelect]![cboDept]) AND
((Format([FLSHIPDATE],"mmmm"))=[Forms]![frmReportSelect]![cboMonth]) AND
((Format([FLSHIPDATE],"yyyy"))=[Forms]![frmReportSelect]![cboYear])) OR
(((Format([FLSHIPDATE],"mmmm"))=[Forms]![frmReportSelect]![cboMonth]) AND
((Format([FLSHIPDATE],"yyyy"))=[Forms]![frmReportSelect]![cboYear]) AND
(([Forms]![frmReportSelect]![cboDept]) Is Null));
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count YTD workdays 2
Out of Stack Space error 4
Problem in Cross tab query 7
opening query in design view closes application 1
CDate results in Data Type Mismatch in Query & Excel Pivot 1

Top