Prev: calculating a percentage
Next: BETWEEN a rock AND a hard place
From: Kingsoft on 29 Jan 2010 10:56
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,
--
kingsoft
From: John Spencer on 29 Jan 2010 11:33
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

Kingsoft wrote:
> 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,
From: Duane Hookom on 29 Jan 2010 11:43
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


"Kingsoft" wrote:

> 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,
> --
> kingsoft
From: Jerry Whittle on 29 Jan 2010 11:45
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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Kingsoft" wrote:

> 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,
> --
> kingsoft
From: Kingsoft on 29 Jan 2010 12:04
Thanks duane,

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

Thanks again!
--
kingsoft


"Duane Hookom" wrote:

> 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
>
>
> "Kingsoft" wrote:
>
> > 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,
> > --
> > kingsoft
 | 
Pages: 1
Prev: calculating a percentage
Next: BETWEEN a rock AND a hard place