Crosstab Query - Kind of Wierd

[Guest]

[Access 2K]

Hi, everyone...

Have a query that uses a reference to a field on a form (a date text box) in
the WHERE clause. Constructed as a simple Select query, it works fine. When
I try to use the exact same reference in a crosstab query, I get the
following error:

The Microsoft Jet database engine does not recognize '[Forms]![AIS
Billing]![BegDate]' as a valid field name or expression.

This occurs if the "WHERE" clause is in the crosstab query itself (see
example below), or in a separate query on which the crosstab query is based.
Here's the SQL:

TRANSFORM Sum(BillingReportsData.OrderQty) AS NumItems
SELECT BillingReportsData.cam_id, BillingReportsData.CAM_Code,
Sum(BillingReportsData.OrderQty) AS [Total Orders]
FROM BillingReportsData
WHERE (((BillingReportsData.order_date)>=[Forms]![AIS Billing]![BegDate] And
(BillingReportsData.order_date)<[Forms]![AIS Billing]![HiddenDate]))
GROUP BY BillingReportsData.cam_id, BillingReportsData.CAM_Code
PIVOT BillingReportsData.product_type;

The "AIS Billing" form is active and the two text boxes contain valid dates.
What's going on?

Thanks,
BruceS

 

[Duane Hookom]

Crosstabs require you to specify the data type of all parameters. Select
Query->Parameters and enter

[Forms]![AIS Billing]![BegDate] Date/Time
[Forms]![AIS Billing]![HiddenDate] Date/Time

 

[Guest]

Thanks, Duane! That did it.
Bruce

Crosstabs require you to specify the data type of all parameters. Select
Query->Parameters and enter

[Forms]![AIS Billing]![BegDate] Date/Time
[Forms]![AIS Billing]![HiddenDate] Date/Time

--
Duane Hookom
MS Access MVP

[Access 2K]

Hi, everyone...

Have a query that uses a reference to a field on a form (a date text box)
in
the WHERE clause. Constructed as a simple Select query, it works fine.
When
I try to use the exact same reference in a crosstab query, I get the
following error:

The Microsoft Jet database engine does not recognize '[Forms]![AIS
Billing]![BegDate]' as a valid field name or expression.

This occurs if the "WHERE" clause is in the crosstab query itself (see
example below), or in a separate query on which the crosstab query is
based.
Here's the SQL:

TRANSFORM Sum(BillingReportsData.OrderQty) AS NumItems
SELECT BillingReportsData.cam_id, BillingReportsData.CAM_Code,
Sum(BillingReportsData.OrderQty) AS [Total Orders]
FROM BillingReportsData
WHERE (((BillingReportsData.order_date)>=[Forms]![AIS Billing]![BegDate]
And
(BillingReportsData.order_date)<[Forms]![AIS Billing]![HiddenDate]))
GROUP BY BillingReportsData.cam_id, BillingReportsData.CAM_Code
PIVOT BillingReportsData.product_type;

The "AIS Billing" form is active and the two text boxes contain valid
dates.
What's going on?

Thanks,
BruceS