Want a Parameter for date range driving a cross tab query
On Thu, 29 Oct 2009 19:17:01 -0700, ChuckW wrote:
Hi,
I have a form with two text boxes called txtStartDate and txtEndDate. I have
a query called qryCuffsShipped1 with a date field called DateShipped. In the
criteria area in design view for this field I have
Between [forms]![frmCuffsShippedMain]![txtStartDate] And
[forms]![frmCuffsShippedMain]![txtEndDate]
The query runs and the date restrictor works. I created a cross tab query
based on this query with a button on the form which runs it. My DateShipped
field is the Column heading in the cross tab. When I run the cross tab
without anything in the criteria statement of qryCuffsShipped1 it works.
However, when I put the above parameter in the DateShipped field of
qryCuffsShipped1 I get an error that says "The Microsoft Jet Database engine
does not recognize
[forms]![frmCuffsShippedMain]![txtStartDate] as a valid field name or
expression.
I want to be able to choose the dates that run a cross tab but am having
trouble doing so. Can someone help?
Thanks,
It's always permissible to explicitly define a query's Parameters. Oddly, for
a Crosstab query, it's obligatory!
Open the Query in SQL view and put
PARAMETERS [forms]![frmCuffsShippedMain]![txtStartDate] DateTime,
[forms]![frmCuffsShippedMain]![txtEndDate] DateTime;
at the very top of the query, before the SELECT statement; or, equivalently,
right click the background of the tables window in query design view, select
Parameters, and put the parameters (exactly, they must match byte for byte) in
the left column and choose Date/Time from the dropdown in the right.
--
John W. Vinson [MVP]
|