using parameter declaration in a crosstab query

J

jrtmax

I would like to declare a parameter in a crosstab query (using a WHERE
clause). However when I do so I get an error saying "The Microsoft Jet
database engine does not recognize '[Summary Date]' as a valid field name or
expression."

I have also tried using a PARAMETERS declaration at the beginning of the SQL
code for this crosstab query. This works out OK for running the query,
however when I link this query to the report it goes with, I am continually
prompted for the parameters while I am designing the report...

Any ideas on how I can fix this?
 
J

John Vinson

I would like to declare a parameter in a crosstab query (using a WHERE
clause). However when I do so I get an error saying "The Microsoft Jet
database engine does not recognize '[Summary Date]' as a valid field name or
expression."

I have also tried using a PARAMETERS declaration at the beginning of the SQL
code for this crosstab query. This works out OK for running the query,
however when I link this query to the report it goes with, I am continually
prompted for the parameters while I am designing the report...

Any ideas on how I can fix this?
Click to expand...

Crosstab queries *require* that parameters be explicitly included in
the Paramters declaration (other queries allow it, but it's not
optional for crosstabs). One way to cut the noise level is to use a
form reference rather than a prompt - if you have a (open, though it
can be invisible) form named frmCrit with a textbox txtSummaryDate,
you can use a criterion of

=[Forms]![frmCrit]![txtSummaryDate]

and the report will look to this for the criterion without yelling at
you.

John W. Vinson[MVP]
 
J

jrtmax

I am opening the report from the database window rather than from a form.
What can I do in this case? Do I have to create a form to use to call the
report?

Thanks - jrtmax

John Vinson said:
I would like to declare a parameter in a crosstab query (using a WHERE
clause). However when I do so I get an error saying "The Microsoft Jet
database engine does not recognize '[Summary Date]' as a valid field name or
expression."

I have also tried using a PARAMETERS declaration at the beginning of the SQL
code for this crosstab query. This works out OK for running the query,
however when I link this query to the report it goes with, I am continually
prompted for the parameters while I am designing the report...

Any ideas on how I can fix this?
Click to expand...

Crosstab queries *require* that parameters be explicitly included in
the Paramters declaration (other queries allow it, but it's not
optional for crosstabs). One way to cut the noise level is to use a
form reference rather than a prompt - if you have a (open, though it
can be invisible) form named frmCrit with a textbox txtSummaryDate,
you can use a criterion of

=[Forms]![frmCrit]![txtSummaryDate]

and the report will look to this for the criterion without yelling at
you.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

I am opening the report from the database window rather than from a form.
What can I do in this case? Do I have to create a form to use to call the
report?
Click to expand...

That, or type in the value of the parameter every time you open the
report. The report cannot open unless you satisfy the parameter.

John W. Vinson[MVP]
 
Top