Filtering on Crosstab queries

[adamburton]

This issue has been dogging me for weeks now and Ive sort of come to the conclusion that it can't be done but here goes:

Can you add a referencing field (eg [forms]![frmform]![cmbfield] etc) onto a crosstab query for filtering records?

Whenever I try I get the following error message:

The Microsoft Jet engine does not recognise '[forms]![frmform]![cmbfield]' as a valid field name or expression.

Ive been encountering this problem when trying to add filtering to a chart report from a combo box on a form, but after stripping it down Ive realised the problem isnt the chart, but the underlying crosstab query.

Please help, as I need to provide a resolution for my boss!!

thanks,
Adam.

 

[dcx693]

I believe that in a crosstab query, you MUST define the parameters in the Query, Parameters.... dialog box before they will work. This is not strictly true for other types of queries, but is for crosstabs.

 

[adamburton]

thank you so much for your quick reply. this might just be the answer ive been looking for.

but...can i ask a stupid question? where do i find the query parameter dialogue box, and what sort of thing do i need to write in there - would i put my [forms]![frmform] etc in there or something else?

thanks,
adam.

 

[dcx693]

In query design view, go to the "Query" menu, choose "Parameters...."

Yes, write exactly what you do in the Criteria line(s) in the QBE grid (use a copy and paste just to be sure) and tell Access what data type it is.

 

[adamburton]

THANK YOU!!!

DCX693,

Thank you...thank you...thank you!!!!!!!!!!!!!!!!!!!!!!!!!! IT WORKED!!! I am soooo grateful

and relieved!!

kind regards,
Adam.

 

[dcx693]

Glad I could help out.