Criteria fails changing Select query to crosstab.

[Phil]

This Works.

SELECT invoice_h.customer_id, [Promopoint List].Heading,
Sum(invoice_d.ship_qty) AS SumOfship_qty
FROM customer_type INNER JOIN ((([Promopoint List] INNER JOIN
([Promopoint Item Translation] INNER JOIN invoice_d ON [Promopoint Item
Translation].item_id = invoice_d.item_id) ON [Promopoint
List].ConcatFromPromopoint = [Promopoint Item
Translation].Concatforheading) INNER JOIN invoice_h ON
(invoice_d.invoice_id = invoice_h.invoice_id) AND (invoice_d.company_id
= invoice_h.company_id)) INNER JOIN customer ON invoice_h.customer_id =
customer.customer_id) ON customer_type.customer_type_id =
customer.customer_type_id
WHERE (((customer_type.name)="FOREIGN DISTRIBUTOR") AND
((invoice_d.company_id)=1) AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]))
GROUP BY invoice_h.customer_id, [Promopoint List].Heading;

IF I change it to a crosstab:

TRANSFORM Sum(invoice_d.ship_qty) AS SumOfship_qty
SELECT invoice_h.customer_id
FROM customer_type INNER JOIN ((([Promopoint List] INNER JOIN
([Promopoint Item Translation] INNER JOIN invoice_d ON [Promopoint Item
Translation].item_id = invoice_d.item_id) ON [Promopoint
List].ConcatFromPromopoint = [Promopoint Item
Translation].Concatforheading) INNER JOIN invoice_h ON
(invoice_d.invoice_id = invoice_h.invoice_id) AND (invoice_d.company_id
= invoice_h.company_id)) INNER JOIN customer ON invoice_h.customer_id =
customer.customer_id) ON customer_type.customer_type_id =
customer.customer_type_id
WHERE (((customer_type.name)="FOREIGN DISTRIBUTOR") AND
((invoice_d.company_id)=1) AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]))
GROUP BY invoice_h.customer_id
PIVOT [Promopoint List].Heading;

I get
The Microsoft Jet database engine does not recognize
'[Forms]![Sales_Reports_Form]![STARTDATE]' as a valid field name or
expression.

Nothing has changed about that form field. I have only changed the
query type. Any ideas?

 

[fredg]

This Works.

SELECT invoice_h.customer_id, [Promopoint List].Heading,
Sum(invoice_d.ship_qty) AS SumOfship_qty
FROM customer_type INNER JOIN ((([Promopoint List] INNER JOIN
([Promopoint Item Translation] INNER JOIN invoice_d ON [Promopoint Item
Translation].item_id = invoice_d.item_id) ON [Promopoint
List].ConcatFromPromopoint = [Promopoint Item
Translation].Concatforheading) INNER JOIN invoice_h ON
(invoice_d.invoice_id = invoice_h.invoice_id) AND (invoice_d.company_id
= invoice_h.company_id)) INNER JOIN customer ON invoice_h.customer_id =
customer.customer_id) ON customer_type.customer_type_id =
customer.customer_type_id
WHERE (((customer_type.name)="FOREIGN DISTRIBUTOR") AND
((invoice_d.company_id)=1) AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]))
GROUP BY invoice_h.customer_id, [Promopoint List].Heading;

IF I change it to a crosstab:

TRANSFORM Sum(invoice_d.ship_qty) AS SumOfship_qty
SELECT invoice_h.customer_id
FROM customer_type INNER JOIN ((([Promopoint List] INNER JOIN
([Promopoint Item Translation] INNER JOIN invoice_d ON [Promopoint Item
Translation].item_id = invoice_d.item_id) ON [Promopoint
List].ConcatFromPromopoint = [Promopoint Item
Translation].Concatforheading) INNER JOIN invoice_h ON
(invoice_d.invoice_id = invoice_h.invoice_id) AND (invoice_d.company_id
= invoice_h.company_id)) INNER JOIN customer ON invoice_h.customer_id =
customer.customer_id) ON customer_type.customer_type_id =
customer.customer_type_id
WHERE (((customer_type.name)="FOREIGN DISTRIBUTOR") AND
((invoice_d.company_id)=1) AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]))
GROUP BY invoice_h.customer_id
PIVOT [Promopoint List].Heading;

I get
The Microsoft Jet database engine does not recognize
'[Forms]![Sales_Reports_Form]![STARTDATE]' as a valid field name or
expression.

Nothing has changed about that form field. I have only changed the
query type. Any ideas?

In a Crosstab query you must set the query parameters and their
datatype in the Query Parameters dialog. Make sure all the parameters
are spelled exactly the same as in their respective criteria.
In Design View click on Query + Parameters