Form Reference for Criteria in a Crosstab (2003 SP1)

Topic

New Reply

Huge screenshot reduced in size by HansV since it caused horizontal scrolling. PLEASE don’t post images larger than 640×480.

I’m stumped. I have a crosstab query where one of the criteria is a reference to a combo box on an open form. When I run the query I get this error.

“The Microsoft Jet database engine does not recognize ‘forms![hrly Trng Report for Supervisor]!ComboCrew’ as a valid field name or expression.”

A picture of the query and form behind it is attached.

What’s causing that error? If I enter the value into the crosstab, the query runs without errors. I’m sure you can reference a form control in a crosstab. I’ve done it in the Northwind db.

Sarah

Reply | Quote

Replies

You must declare the parameter explicitly:

Open the query in design view.
Select Query | Parameters…
Enter the parameter exactly as it’s used in the Criteria line, and select the appropriate data type. Then click OK.

Reply | Quote

Oh you are so smart.

That worked. Forgot about that. I take it that’s necessary with crosstabs. I know it isn’t for regular select queries.

Thanks for the help.

Reply | Quote

See ACC2000: Error When Running Crosstab Query with a Parameter for an explanation why it is necessary for crosstab queries.

Reply | Quote