Three related queries and parameter issue

[scubadiver007]

I can export a query directly to an Excel spreadsheet using a module which works find but I would like to filter the data further using a drop down box but I get the following error:

Too few parameters. Expected 0.

The first is a union query

[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]select [ID_Service],[Field_ID], [quarter_ID], [practice_code], [activity][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]from [tble_patientactivity][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]where [ID_Service]=90300 and [quarter_ID]= [forms]![form_payreportmenu]![PRQuarter] [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]UNION select [ID_Service],[Field_ID], [quarter_ID], [practice_code], [activity][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]from [tble_manentry][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]where [ID_Service]=90300 and [quarter_ID]= [forms]![form_payreportmenu]![PRQuarter];[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

This is the source for a cross-tab query, which is fixed. The number of columns don't change.

[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]PARAMETERS [forms]![form_payreportmenu]![PRQuarter] Text ( 255 );[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]TRANSFORM Sum(Qry_Report_CVD.activity) AS SumOfactivity[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]SELECT Qry_Report_CVD.practice_code, Qry_Report_CVD.quarter_ID[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM Qry_Report_CVD[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]WHERE (((Qry_Report_CVD.quarter_ID)=[forms]![form_payreportmenu]![PRQuarter]))[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]GROUP BY Qry_Report_CVD.practice_code, Qry_Report_CVD.quarter_ID[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]PIVOT Qry_Report_CVD.Field_ID;[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

This is the source for a third query, which I want to export:

[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]SELECT Qry_Report_CVD_CT.quarter_ID, Qry_Report_CVD_CT.[90301][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM Tble_Practice LEFT JOIN Qry_Report_CVD_CT ON Tble_Practice.Practice_Code = Qry_Report_CVD_CT.practice_code[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]WHERE (((Qry_Report_CVD_CT.quarter_ID)=[forms]![form_payreportmenu]![PRQuarter]));[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

I think it is something to do with jet not recognising the filter as coming from a form. Is this correct? How do I resolve it.

 

[scubadiver007]

Cany anyone help?

 

[Taruz]

Hi..

The first query edit it..:

select * 

from (
          select [ID_Service],[Field_ID], [quarter_ID], [practice_code], [activity]
            from [tble_patientactivity]

union 
            select [ID_Service],[Field_ID], [quarter_ID], [practice_code], [activity]
                from [tble_manentry]
            ) as trz
where [ID_Service]=90300 
                 and 
             [quarter_ID]= eval("[forms]![form_payreportmenu]![PRQuarter]")

Also...;

No need to use the same criteria in the cross-query seems to ..

 

[scubadiver007]

I've sorted it out. Thanks very much!