Cross Tab on Select Query

[gazzieh]

Can you base a cross-tab query on a select query that requires a parameter?

I ask this because I have tried but so far it has failed; raising an error related to the [forms]![formname]![field] statement used in the select query.

I know the select query does work and does what I need it to so the error is not there.

[NassauBob]

You can do this. When I tried to recreate your error condition, and the error message popped up, I clicked on Help. It opened the help on parameters and crosstab queries. Here is what Microsoft explicitly stated about this error:

The Microsoft Jet database engine does not recognize <name> as a valid field name or expression. (Error 3070)
The specified name is not a recognized field name or a valid expression. In a query, this error can occur if you enter a name that improperly refers to a database, table, or field.

Possible causes with Microsoft® Access:

You have a parameter in a crosstab query or in a query that a crosstab query or chart is based on, and the parameter data type is not explicitly specified in the Query Parameters dialog box. To solve the problem:

1. In the query that contains the parameter, specify the parameter and its data type in the Query Parameters dialog box. And;
2. Set the ColumnHeadings property for the query that contains the parameter.


I did this, and it worked! I did not enter datatype anywhere for #1, but to do #2 above, open the query in design view, and click on the properties icon. In the next form, enter the name of the 'parameterized' column in Column Headings.

I also moved the value parameter from the underlying select query to the crosstab one, but I do not think that makes a difference.