Want a Parameter for date range driving a cross tab query

[ChuckW]

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,

 

[John W. Vinson]

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.

 

[Birgit A]

Thank you very much, John. This was just the information I came looking for.

I would like to add for others who need this information that I had to
remove extra [] via SQL view after I added the parameter via the design view
(kept getting an error about exta []).

Cheers,
Birgit