Criteria in a Crosstab Query

[Donna Brooks]

I have created a query that I used to create a crosstab
query.
QryforCrosstab - query used to create crosstab
QryforCrosstab_crosstab - Crosstab query

I can specify the criteria [Enter the last day of the
month] in QryforCrosstab, but when I run the
QryforCrosstab_crosstab, it gives me an error stating
that the Jet engine doesn't recognize the field [Enter
the last day of the month]. It is recognizing it as a
field, instead of a prompt for criteria. How do I prompt
my crosstab query to only give me records where the
DateNextDue is On or Before a specified date.

TIA,
Donna Brooks

 

[Nikos Yannacopoulos]

Donna,

Unlike other query types, crosstabs require that user parameter entry in
criteria be explicitly declared as a parameter.
While in query design go to menu path Query > Parameters and type in the
exact test you have in aquare brackets in your criterion, and assign the
appropriate type (date/time, text, numeric etc.).
This should solve your problem.

HTH,
Nikos

 

[Guest]

When I pull up the Parameters dialog box and put in
[Enter the last day of the month] and data type as
date/time, how does it know that the criteria is for
DateNextDue. Do I leave the criteria row blank in query
design under DateNextDue? Also, I need to put a <= in
front of my criteria, b/c I need to pull all shots due on
or before the last day of the month.

TIA,
Donna Brooks

-----Original Message-----
Donna,

Unlike other query types, crosstabs require that user parameter entry in
criteria be explicitly declared as a parameter.
While in query design go to menu path Query > Parameters and type in the
exact test you have in aquare brackets in your criterion, and assign the
appropriate type (date/time, text, numeric etc.).
This should solve your problem.

HTH,
Nikos

I have created a query that I used to create a crosstab
query.
QryforCrosstab - query used to create crosstab
QryforCrosstab_crosstab - Crosstab query

I can specify the criteria [Enter the last day of the
month] in QryforCrosstab, but when I run the
QryforCrosstab_crosstab, it gives me an error stating
that the Jet engine doesn't recognize the field [Enter
the last day of the month]. It is recognizing it as a
field, instead of a prompt for criteria. How do I prompt
my crosstab query to only give me records where the
DateNextDue is On or Before a specified date.

TIA,
Donna Brooks

.

 

[John Spencer (MVP)]

When you define a parameter that is all you are doing. You are telling the
program that this name "[Enter the last day of the month]"is a parameter and it
is this type of parameter "Date/Time". Then you can use the parameter where you
need it. So in this case, what you need is to define the parameter as you have.
THEN you need to use it, by putting it into a criteria "cell" as

<=[Enter the last day of the month]

When I pull up the Parameters dialog box and put in
[Enter the last day of the month] and data type as
date/time, how does it know that the criteria is for
DateNextDue. Do I leave the criteria row blank in query
design under DateNextDue? Also, I need to put a <= in
front of my criteria, b/c I need to pull all shots due on
or before the last day of the month.

TIA,
Donna Brooks

-----Original Message-----
Donna,

Unlike other query types, crosstabs require that user parameter entry in
criteria be explicitly declared as a parameter.
While in query design go to menu path Query > Parameters and type in the
exact test you have in aquare brackets in your criterion, and assign the
appropriate type (date/time, text, numeric etc.).
This should solve your problem.

HTH,
Nikos

I have created a query that I used to create a crosstab
query.
QryforCrosstab - query used to create crosstab
QryforCrosstab_crosstab - Crosstab query

I can specify the criteria [Enter the last day of the
month] in QryforCrosstab, but when I run the
QryforCrosstab_crosstab, it gives me an error stating
that the Jet engine doesn't recognize the field [Enter
the last day of the month]. It is recognizing it as a
field, instead of a prompt for criteria. How do I prompt
my crosstab query to only give me records where the
DateNextDue is On or Before a specified date.

TIA,
Donna Brooks

.