Query works, query based on it fails.

[Phil Smith]

I have a base query, which pulls out some data which I intend to do
various things with. It works fine.:

SELECT invoice_h.invoice_date, invoice_h.customer_id, invoice_h.status,
invoice_d.item_id, item.item_type, item.division_id, item.brand_id,
customer.territory_id, customer.customer_type_id, invoice_d.price,
invoice_d.ship_qty
FROM customer INNER JOIN (item INNER JOIN (invoice_d INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
item.item_id = invoice_d.item_id) ON customer.customer_id =
invoice_h.customer_id
WHERE (((invoice_h.invoice_date)>=[Forms]![Report Card Form]![Start] And
(invoice_h.invoice_date)<=[Forms]![Report Card Form]![End]) AND
((invoice_h.status)=8 Or (invoice_h.status)=9)) OR
(((invoice_h.invoice_date)>=[Forms]![Report Card Form]![SecStart] And
(invoice_h.invoice_date)<=[Forms]![Report Card Form]![SecStop]) AND
((invoice_h.status)=8 Or (invoice_h.status)=9));

This is called "base query.

When I run another query which uses Base Query, I get an error saying
that "The Microsoft Jet database engine does not recognize
'[Forms]![Report Card Form]![Start]' As you can see, the only reference
to that Form Field is in "Base Query". Any Ideas?


TRANSFORM Sum([Base Query]![price]*[Base Query]![ship_qty]) AS Sales
SELECT customer.name AS Customer, brand.[name] AS Brand
FROM item_types RIGHT JOIN (customer RIGHT JOIN (brand RIGHT JOIN [Base
Query] ON brand.brand_id = [Base Query].brand_id) ON
customer.customer_id = [Base Query].customer_id) ON item_types.type_id =
[Base Query].item_type
GROUP BY [Base Query].customer_id, customer.[name], brand.[name]
ORDER BY customer.[name], brand.[name], [item_types]![name]
PIVOT [item_types]![name];

 

[KARL DEWEY]

Crosstab queries must have parameters declared.

Open the crosstab query in design view and click on Query - Parameters.
Enter [Forms]![Report Card Form]![Start] in the parameter field and text, 255
in the datatype.

 

[Baz]

For a crosstab query all the parameters have to be explicitly declared, even
if they are in a subsidiary query. The following article explains:

http://support.microsoft.com/kb/209778/en-us