I am trying to run a crosstab query with a parameter that uses a hidden control on a form


the crosstab works fine if I hand code the criteria in the ShowID column - for example 160 - returns all records for show 160

if I use the following as the criteria [Forms]![frmShows]![ShowID] - I get an error

see image attached for the query

How can i use a form field as the criteria in a crosstab query?

Thanks


query syntax is below

SELECT qryCustomReport_Exhibitors_Union.BoothNo, qryCustomReport_Exhibitors_Union.LastName, qryCustomReport_Exhibitors_Union.FirstName, qryCustomReport_Exhibitors_Union.CompanyName, qryCustomReport_AllCrosstabFields.*, qryCustomReport_Exhibitors_Union.ShowID
FROM qryCustomReport_Exhibitors_Union INNER JOIN qryCustomReport_AllCrosstabFields ON qryCustomReport_Exhibitors_Union.ExhibitorRegID = qryCustomReport_AllCrosstabFields.ExhibitorRegID
WHERE (((qryCustomReport_Exhibitors_Union.Type)="primary") AND ((qryCustomReport_Exhibitors_Union.Status)<>"cancelled") AND ((qryCustomReport_Exhibitors_Union.ShowID)=[Forms]![frmShows]![ShowID]))
ORDER BY qryCustomReport_Exhibitors_Union.BoothNo;

Hi,

One quick solution is to wrap the parameter in an Eval() function. For example:

Eval("Forms!frmShows.ShowID")

Just my 2 cents...

Hi
Check out: Error When Running Crosstab Query with a Parameter..

Pay no attention to the version, it applies to all versions..

HTH's