Prev: Why does this query ask for the start date and end date twice?
Next: Sort on a calculated field
From: Renee on 14 Apr 2010 19:28
I created a parameter query, and then I created a crosstab query based on the
first parameter query. I keep getting an error message that says: "The
Microsoft Office Access database engine does not recognize '[start date]' as
a valid field name or expression."

In more detail:
I created a query where I have a pop up parameter box that asks for the
start date and another asking for the end date. The parameter is written in
the criteria as follows:

Between [start date mm/dd/yyyy] and [end date mm/dd/yyyy]

When I run the query, the parameter query works beautifully.

So then, I ran a crosstab query based on the (first) parameter query hoping
that it would pull the parameter query forward, but it didn't, I got the
error message instead.

I ran it past my expert who has been doing Access DBs for years and teaches
Access to students, and she suggested that I do the criteria as follows:

Between 2/1/2010 And 2/28/2010

And it worked like a charm for both the regular query and the crosstab
query; however, I lost the function of the parameter pop up box.

Any suggestions?
From: John Spencer on 14 Apr 2010 20:45
First thing, 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

repeat for all parameters

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

Renee wrote:
> I created a parameter query, and then I created a crosstab query based on the
> first parameter query. I keep getting an error message that says: "The
> Microsoft Office Access database engine does not recognize '[start date]' as
> a valid field name or expression."
>
> In more detail:
> I created a query where I have a pop up parameter box that asks for the
> start date and another asking for the end date. The parameter is written in
> the criteria as follows:
>
> Between [start date mm/dd/yyyy] and [end date mm/dd/yyyy]
>
> When I run the query, the parameter query works beautifully.
>
> So then, I ran a crosstab query based on the (first) parameter query hoping
> that it would pull the parameter query forward, but it didn't, I got the
> error message instead.
>
> I ran it past my expert who has been doing Access DBs for years and teaches
> Access to students, and she suggested that I do the criteria as follows:
>
> Between 2/1/2010 And 2/28/2010
>
> And it worked like a charm for both the regular query and the crosstab
> query; however, I lost the function of the parameter pop up box.
>
> Any suggestions?
From: Renee on 15 Apr 2010 13:02
OMG! You do not understand how much this problem has been plaguing me. I
knew it had to be something relatively simple. THANK YOU, SO MUCH!!!!! You
are awesome!

"John Spencer" wrote:

> First thing, 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
>
> repeat for all parameters
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Renee wrote:
> > I created a parameter query, and then I created a crosstab query based on the
> > first parameter query. I keep getting an error message that says: "The
> > Microsoft Office Access database engine does not recognize '[start date]' as
> > a valid field name or expression."
> >
> > In more detail:
> > I created a query where I have a pop up parameter box that asks for the
> > start date and another asking for the end date. The parameter is written in
> > the criteria as follows:
> >
> > Between [start date mm/dd/yyyy] and [end date mm/dd/yyyy]
> >
> > When I run the query, the parameter query works beautifully.
> >
> > So then, I ran a crosstab query based on the (first) parameter query hoping
> > that it would pull the parameter query forward, but it didn't, I got the
> > error message instead.
> >
> > I ran it past my expert who has been doing Access DBs for years and teaches
> > Access to students, and she suggested that I do the criteria as follows:
> >
> > Between 2/1/2010 And 2/28/2010
> >
> > And it worked like a charm for both the regular query and the crosstab
> > query; however, I lost the function of the parameter pop up box.
> >
> > Any suggestions?
> .
>
 | 
Pages: 1
Prev: Why does this query ask for the start date and end date twice?
Next: Sort on a calculated field