Crosstab Query - Kind of Wierd

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thanks, Duane! That did it.
Bruce

Duane Hookom said:
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


BruceS said:
[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
Click to expand...
Click to expand...
 
Back
Top