User Filter for Cross-Tab Query

[VinKid]

I have data that is output from a measurement device and I am building reports for the data through access. Currently I have a croostab querry that will provide the data that I need, but I need to allow the user to filter by a specific field to limit the report to one specific part. Normally (with a Select Query) I do this by creating a form with a combo box which will allow the user to choose the part, embed that combo box selection as the criteria in the query and launch the report. The full expression in the criteria is: Like [Forms]![Frm_832_SN]![Input_832_SN] & "*"

Using a croos tab query with this method results in the following error.

The Microsoft Jet database engine does not recognize [Forms]![Frm_832_SN]![Cmb_832_SN] as a valid field name or expression. (Error 3070)

Any help you can provide would be appreciated.

Vinkid

 

[Trevor G]

You may have to pass your cross tab into a temp table to enable you to use the search filter.

 

[VinKid]

Trevor G,
Thank you for your response. However, I am not sure what you mean exactly.

Convert my Crosstab query into a table and then use a Select query on this new table? Would the table be dynamic, i.e. update whenever the Crosstab query is run in the background?

This may defeat the reason for using the cross tab for my report in the first palce as well, I would need to think about that one...

Vinkid

 

[Trevor G]

Have a look at this link see if it helps.

http://allenbrowne.com/ser-67.html

 

[VinKid]

TrevorG,

Thank you for the link. I am posting the informaiton form that link that solved the problem, for future reference. I needed to declare the parameter for the Crosstab Query.

Handle parameters

A query can ask you to supply a value at runtime. It pops up a parameter dialog if you enter something like this:
[What order date]
Or, it can read a value from a control on a form:
[Forms].[Form1].[StartDate]
But, parameters do not work with crosstab queries, unless you:
a) Declare the parameter, or
b) Specify the column headings.
To declare the parameter, choose Parameters on the Query menu. Access opens a dialog. Enter the name and specify the data type. For the examples above, use the Query Parameters dialog like this:

<TABLE style="BORDER-COLLAPSE: collapse" width=420 border=1><TBODY><TR><TD align=middle width="50%" bgColor=#ece9d8>Parameter</TD><TD align=middle bgColor=#ece9d8>Data Type</TD></TR><TR><TD>[What order date]</TD><TD>Date/Time</TD></TR><TR><TD>[Forms].[Form1].[StartDate]</TD><TD>Date/Time</TD></TR><TR><TD> </TD><TD> </TD></TR><TR><TD align=middle>[ OK ]</TD><TD align=middle>[ Cancel ]</TD></TR></TBODY></TABLE>​

Declaring your parameters is always a good idea (except for an Access bug in handling parameters of type Text), but it is not essential if you specify your column headings.

Regards,

Vinkid