crosstab Date parameter

  • Thread starter Thread starter JT
  • Start date Start date
J

JT

I have a crosstab query .In the query design view I have
a "date" field, I'm trying to make sort by the following
parameters.

between [enter start date]and [enter end date]

it comes back with an error "The Microsoft Jet database
engine does not recognize <name> as a valid field name or
expression. how can I get this to work."
 
In query design, choose Parameters from the Query menu and explicitly
declare your two parameters as date/time. Many parameter queries will work
without doing this, but Crosstab queries require you to declare your
parameters. It's a good idea to always declare your parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
JT,

Regrettably this does not work for passing user input to crosstabs, nor does
taking the criterion value from a form... nor can you trick it by imposing
the criterion on a select query and basing the crosstab on it, it returns
the same error message.

The only way I know around it is by using a VB function that returns the
criterion value; this is accepted as a criterion in the crosstab.

HTH,
Nikos
 
once I do that and run it it still give me all the dates.
in that parmeter box. I put

[Begin Date] Date/Time
[End Date} Date/Time

when I push run it give me all the dates

ex. 6-1-02 to 6-1-03
it give all records including 1991
-----Original Message-----
In query design, choose Parameters from the Query menu and explicitly
declare your two parameters as date/time. Many parameter queries will work
without doing this, but Crosstab queries require you to declare your
parameters. It's a good idea to always declare your parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I have a crosstab query .In the query design view I have
a "date" field, I'm trying to make sort by the following
parameters.

between [enter start date]and [enter end date]

it comes back with an error "The Microsoft Jet database
engine does not recognize <name> as a valid field name or
expression. how can I get this to work."
Click to expand...


.
Click to expand...
 
Please post the SQL from your query. (Open your query in design view,
choose SQL from the View menu, and copy and paste the text you see in a
reply.) Are you running on a non-U.S. machine?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
JT again said:
once I do that and run it it still give me all the dates.
in that parmeter box. I put

[Begin Date] Date/Time
[End Date} Date/Time

when I push run it give me all the dates

ex. 6-1-02 to 6-1-03
it give all records including 1991
-----Original Message-----
In query design, choose Parameters from the Query menu and explicitly
declare your two parameters as date/time. Many parameter queries will work
without doing this, but Crosstab queries require you to declare your
parameters. It's a good idea to always declare your parameters.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I have a crosstab query .In the query design view I have
a "date" field, I'm trying to make sort by the following
parameters.

between [enter start date]and [enter end date]

it comes back with an error "The Microsoft Jet database
engine does not recognize <name> as a valid field name or
expression. how can I get this to work."
Click to expand...


.
Click to expand...
Click to expand...
 
Sorry Nikos but you are mistaken. John Viescas suggests the proper method of
including parameters in crosstab queries. If JT is still having a problem,
he/she needs to reply with his/her SQL view of the crosstab so that someone
can suggest a correction.

--
Duane Hookom
MS Access MVP

Nikos Yannacopoulos said:
JT,

Regrettably this does not work for passing user input to crosstabs, nor does
taking the criterion value from a form... nor can you trick it by imposing
the criterion on a select query and basing the crosstab on it, it returns
the same error message.

The only way I know around it is by using a VB function that returns the
criterion value; this is accepted as a criterion in the crosstab.

HTH,
Nikos

JT said:
I have a crosstab query .In the query design view I have
a "date" field, I'm trying to make sort by the following
parameters.

between [enter start date]and [enter end date]

it comes back with an error "The Microsoft Jet database
engine does not recognize <name> as a valid field name or
expression. how can I get this to work."
Click to expand...
Click to expand...
 
Duane,

I have to admit I had not tried that... just like sometimes you're looking
for something and can't find it although it's right there in front of you?
Therefore I resorted to the only workaround I could find that worked, which,
as I said, is the only way I knew (I know better now!), not the best or only
way there is.
The part I like is there's always new stuff to learn!

Nikos

Duane Hookom said:
Sorry Nikos but you are mistaken. John Viescas suggests the proper method of
including parameters in crosstab queries. If JT is still having a problem,
he/she needs to reply with his/her SQL view of the crosstab so that someone
can suggest a correction.

--
Duane Hookom
MS Access MVP

Nikos Yannacopoulos said:
JT,

Regrettably this does not work for passing user input to crosstabs, nor does
taking the criterion value from a form... nor can you trick it by imposing
the criterion on a select query and basing the crosstab on it, it returns
the same error message.

The only way I know around it is by using a VB function that returns the
criterion value; this is accepted as a criterion in the crosstab.

HTH,
Nikos

JT said:
I have a crosstab query .In the query design view I have
a "date" field, I'm trying to make sort by the following
parameters.

between [enter start date]and [enter end date]

it comes back with an error "The Microsoft Jet database
engine does not recognize <name> as a valid field name or
expression. how can I get this to work."
Click to expand...
Click to expand...
Click to expand...
 
Back
Top