USING PARAMETERS IN A CROSSTAB QUERY

[Kingsoft]

Hello,

Does Access allow the use of parameters in crosstab queries? I have
attempted to add a parameter in my query but get the following error message:
The Microsoft Jet Database Engine does not recognize '[test]' as a valid
fieldname or expression.

Any help would be greatly appreciated.

Thanks,

 

[John Spencer]

With a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

You must identify the data type of all parameters in crosstabs. Select
Query->Parameters and enter your parameters and select their data types.

If you use the Column Headings property of the crosstab you generally don't
have to do this but it is still good practice.

 

[Jerry Whittle]

Yes but there are a couple of tricks of the trade.

1. Create a regular select query that has the parameter. Pick out the fields
and criteria that you want to use for the crosstab.

2. In newer versions of Access, the parameter must be defined. In Access
2007 open the query in Design View. Next go to the Design tab. Click on
Parameters in the Show/Hide box. You need to have the exact wording as in the
parameter in the criteria line and select the proper data type.

3. Run this query to make sure it works and save it.

4. Base a crosstab query on this first query. In other words make the query
the record source of the crosstab.

 

[Kingsoft]

Thanks duane,

I stumbled across the answer, which was exactly what you suggested, just
prior to getting your response.

Thanks again!
--
kingsoft

You must identify the data type of all parameters in crosstabs. Select
Query->Parameters and enter your parameters and select their data types.

If you use the Column Headings property of the crosstab you generally don't
have to do this but it is still good practice.

--
Duane Hookom
Microsoft Access MVP

Hello,

Does Access allow the use of parameters in crosstab queries? I have
attempted to add a parameter in my query but get the following error message:
The Microsoft Jet Database Engine does not recognize '[test]' as a valid
fieldname or expression.

Any help would be greatly appreciated.

Thanks,