help ... similiar to parameter query; complex query

[margaret]

I'm having a similar problem as the above mentioned question. I have a query
....

PARAMETERS [Work Date] DateTime;
SELECT [TBL Gates].[term#], [TBL Tickets].id, [TBL Tickets].Fullname, [TBL
Tickets].Gates, [TBL Gates].termid, [TBL Tickets].CurrentDate, [TBL
Tickets].Bankdate, [QRYTktSold_Crosstab Query].TotalTKTSold,
[QRYTKTSales_Crosstab Query].Adult2, [QRYTKTSales_Crosstab Query].Child2,
[QRYTKTSales_Crosstab Query].Special2, [QRYTKTSales_Crosstab Query].[Sr
Citizen2], [QRYTKTSales_Crosstab Query].gg2
FROM [TBL Gates] INNER JOIN (([TBL Tickets] INNER JOIN [QRYTktSold_Crosstab
Query] ON [TBL Tickets].id = [QRYTktSold_Crosstab Query].id) INNER JOIN
[QRYTKTSales_Crosstab Query] ON [TBL Tickets].id = [QRYTKTSales_Crosstab
Query].id) ON [TBL Gates].gates = [TBL Tickets].Gates
WHERE ((([TBL Tickets].CurrentDate)=[Work Date]))
ORDER BY [TBL Tickets].Gates;

I can run the query, however, when using it for a report, I get "Syntex
Error in Parameter clause".

Help ... These reports ran fine last year under Access 2003 but am having
trouble running them under Access 2007.

 

[margaret]

I can now get the report to run. I went into the properties of the report
and viewed the query and did a "save as" and made it an actual query as
opposed to just having it in the report. However, now when I run the report,
it asks me for my criteria "Work Date?" five to six times before the report
will run. If I go to the actual and run it, it only asks me for the
criteria once. Here is the SQL for the query ...
PARAMETERS [Bank Date?] DateTime;
SELECT [TBL Tickets].id, [TBL Tickets].Fullname, [TBL Tickets].Gates, [TBL
Gates].termid, [TBL Tickets].CurrentDate, [TBL Tickets].Bankdate,
QRYCash.GrandTotal, QRYDiscounts.[total$], [QRYTktSold_Crosstab
Query].TotalTKTSold, [QRYTKTSales_Crosstab Query].Adult2,
[QRYTKTSales_Crosstab Query].Child2, [QRYTKTSales_Crosstab Query].Special2,
[QRYTKTSales_Crosstab Query].[Sr Citizen2], [QRYTKTSales_Crosstab Query].gg2,
[QRYTktSold_Crosstab Query].gg1
FROM [TBL Gates] INNER JOIN (QRYDiscounts INNER JOIN (QRYCash INNER JOIN
(([TBL Tickets] INNER JOIN [QRYTktSold_Crosstab Query] ON [TBL Tickets].id =
[QRYTktSold_Crosstab Query].id) INNER JOIN [QRYTKTSales_Crosstab Query] ON
[TBL Tickets].id = [QRYTKTSales_Crosstab Query].id) ON QRYCash.id = [TBL
Tickets].id) ON QRYDiscounts.[TBL Discount].id = [TBL Tickets].id) ON [TBL
Gates].gates = [TBL Tickets].Gates
WHERE ((([TBL Tickets].Bankdate)=[Bank Date?]));

The problem may be with the report, however, I don't know where in the
report to look for criteria.

 

[margaret]

To anyone else with this problem, it seems that if you use a named query to
select your fields for the report, you won't get the Syntex Error in
Parameter clause. However, when I open the report, it asks me for the
criteria 6 times. Considering on some reports I have two criteria, it asking
me for info 12 times is a bit of a hassle. If anyone knows the solution, I
would appeciate it.

 

[John W. Vinson]

To anyone else with this problem, it seems that if you use a named query to
select your fields for the report, you won't get the Syntex Error in
Parameter clause. However, when I open the report, it asks me for the
criteria 6 times. Considering on some reports I have two criteria, it asking
me for info 12 times is a bit of a hassle. If anyone knows the solution, I
would appeciate it.

Use a Form to specify the criterion. Each place which needs a criterion should
have

=[Forms]![YourFormName]![SomeControlName]

on the criteria line. So long as the form is open, you won't get prompted even
once, much less repeatedly.

It's always a good idea to open the query's Parameters dialog (Query...
Parameters on the menu, or right mouseclick the background of the tables
window in query design and select Paramters) and specify each parameter and
its datatype.

John W. Vinson [MVP]

 

[margaret]

Thanks, John. I just wanted to let you know I did what you said and it
worked great. It's probably easier for the user this way.

Thanks for all your help.

To anyone else with this problem, it seems that if you use a named query to
select your fields for the report, you won't get the Syntex Error in
Parameter clause. However, when I open the report, it asks me for the
criteria 6 times. Considering on some reports I have two criteria, it asking
me for info 12 times is a bit of a hassle. If anyone knows the solution, I
would appeciate it.

Use a Form to specify the criterion. Each place which needs a criterion should
have

=[Forms]![YourFormName]![SomeControlName]

on the criteria line. So long as the form is open, you won't get prompted even
once, much less repeatedly.

It's always a good idea to open the query's Parameters dialog (Query...
Parameters on the menu, or right mouseclick the background of the tables
window in query design and select Paramters) and specify each parameter and
its datatype.

John W. Vinson [MVP]