Parameterizing in a Cross Tab Query


I

Iram

Hello,
I have a crosstab query called "qry_case_cross_crosstab" that runs against a
table just fine however I need to change it so that the results are narrowed
by using a date range.

So I created a regular select query called "qry_case_cross_crosstabSOURCE"
with a parameter >=[Forms]![frm_Rpt]![BeginDate] And
<=[Forms]![frm_Rpt]![EndDate] on the “Date†field. The
"qry_case_cross_crosstab" points to the "...SOURCE" query for the narrowed
results.

I also created a form called “frm_Rpt†that has a "Begin Date" and an "End
Date".

However when I run the "qry_case_cross_crosstab" (the first query) I get the
error “The Microsft Jet database engine does not recognize
‘[Forms]![frm_Rpt]![BeginDate]’ as a valid field name or expression.

So then on the “…SOURCE†query I clicked Query, then Parameters at the top
of the screen and pasted the below into the “Parameter†field and for the
Data Type I selected “Date\Timeâ€.
=[Forms]![frm_Rpt]![BeginDate] And <=[Forms]![frm_Rpt]![EndDate]

And now I get the below error…
Invalid bracketing of name ‘[>=[Forms]![frm_Rpt]![BeginDate] And
<=[Forms]![frm_Rpt]![EndDate]]

Can you help me mitigate this problem with parameterizing a crosstab?

Your help is greatly appreciated!

Iram/mcp
 
Ad

Advertisements

K

KARL DEWEY

So then on the “…SOURCE†query I clicked Query,
Do the pasting in the crosstab query BUT omit the logical symbols. Remove
them from your “…SOURCE†query also.

--
Build a little, test a little.


Iram said:
Hello,
I have a crosstab query called "qry_case_cross_crosstab" that runs against a
table just fine however I need to change it so that the results are narrowed
by using a date range.

So I created a regular select query called "qry_case_cross_crosstabSOURCE"
with a parameter >=[Forms]![frm_Rpt]![BeginDate] And
<=[Forms]![frm_Rpt]![EndDate] on the “Date†field. The
"qry_case_cross_crosstab" points to the "...SOURCE" query for the narrowed
results.

I also created a form called “frm_Rpt†that has a "Begin Date" and an "End
Date".

However when I run the "qry_case_cross_crosstab" (the first query) I get the
error “The Microsft Jet database engine does not recognize
‘[Forms]![frm_Rpt]![BeginDate]’ as a valid field name or expression.

So then on the “…SOURCE†query I clicked Query, then Parameters at the top
of the screen and pasted the below into the “Parameter†field and for the
Data Type I selected “Date\Timeâ€.
=[Forms]![frm_Rpt]![BeginDate] And <=[Forms]![frm_Rpt]![EndDate]

And now I get the below error…
Invalid bracketing of name ‘[>=[Forms]![frm_Rpt]![BeginDate] And
<=[Forms]![frm_Rpt]![EndDate]]

Can you help me mitigate this problem with parameterizing a crosstab?

Your help is greatly appreciated!

Iram/mcp
 
Ad

Advertisements

I

Iram

Thanks Karl, You are the man!

I ended up created two parameters under Query/Parameters like the below on
both queries...

[Forms]![frm_Rpt]![BeginDate] Date/Time
[Forms]![frm_Rpt]![EndDate] Date/Time



Again Thanks!
You guys are awesome!


Iram/mcp




KARL DEWEY said:
Do the pasting in the crosstab query BUT omit the logical symbols. Remove
them from your “…SOURCE†query also.

--
Build a little, test a little.


Iram said:
Hello,
I have a crosstab query called "qry_case_cross_crosstab" that runs against a
table just fine however I need to change it so that the results are narrowed
by using a date range.

So I created a regular select query called "qry_case_cross_crosstabSOURCE"
with a parameter >=[Forms]![frm_Rpt]![BeginDate] And
<=[Forms]![frm_Rpt]![EndDate] on the “Date†field. The
"qry_case_cross_crosstab" points to the "...SOURCE" query for the narrowed
results.

I also created a form called “frm_Rpt†that has a "Begin Date" and an "End
Date".

However when I run the "qry_case_cross_crosstab" (the first query) I get the
error “The Microsft Jet database engine does not recognize
‘[Forms]![frm_Rpt]![BeginDate]’ as a valid field name or expression.

So then on the “…SOURCE†query I clicked Query, then Parameters at the top
of the screen and pasted the below into the “Parameter†field and for the
Data Type I selected “Date\Timeâ€.
=[Forms]![frm_Rpt]![BeginDate] And <=[Forms]![frm_Rpt]![EndDate]

And now I get the below error…
Invalid bracketing of name ‘[>=[Forms]![frm_Rpt]![BeginDate] And
<=[Forms]![frm_Rpt]![EndDate]]

Can you help me mitigate this problem with parameterizing a crosstab?

Your help is greatly appreciated!

Iram/mcp
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top