Solved Crosstab Query with Form Input? (1 Viewer)

[wmix]

I'm not sure if this is possible, but I would appreciate insight into a problem I'm having.

Note, I'm using Access 2000.

Query # 1: qryProductsSoldExpired
This is a basic query that pulls information from two tables. The first table has all of the sales data (sold and expired products, etc). The second table is simply to get the ProductName based on the ProductID from the first table. This simple Select Query works perfect.

Query # 2: qryProductsExpired_Yearly
This crosstab query gives me the Sum of products expired, by year, by ProductID/ProductName.

Query # 3: qryProductsSold_Yearly
This crosstab query gives me the Sum of products sold, by year, by ProductID/ProductName.

What I now want to do is narrow down these queries based on a specific vending machine (MachineID) from query 1.

When I add a vending machine ID (MachineID) into query 1 and Save the query, I can then open query 2 and 3 just fine. However, if I use a form, I created simple form with a Combo Box to pull the MachineID, I can only get the first query to work, crosstab queries 2 and 3 fail.

Here's my information:

Query # 1:
Field: MachineID
Criteria: [Forms]![frmCandySoldExpired_byMachine]![MachineID]

When I click on my "run" button the query processes perfect. However, if I try to open crosstab query 2 or 3 I get this error:

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

Thoughts anyone? Is it even possible to have a crosstab query populate results based on a form value?

Thank you in advance for your assistance.

 

[isladogs]

This post was moderated. Posting to trigger email notifications

 

[CJ_London]

Is it even possible to have a crosstab query populate results based on a form value?

you need to declare your parameters - in 2007 and later there is an option on the ribbon. I haven't used 2000 for over 15 years and can't remember if there is an equivalent in the query design.

The sql would look something like

PARAMETERS [Forms]![frmcandySoldExpired_byMachine]![MachineID] Long;
SELECT …..