Parameterizing in a Cross Tab Query

  • Thread starter Thread starter Iram
  • Start date Start date
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]
Click to expand...

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
 
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]
Click to expand...

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
Click to expand...
 
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]
Click to expand...

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
Click to expand...
Click to expand...
 
Back
Top