Trouble with union qry

[tsison7]

Below is a union query which gathers up shipment and returns to give data
representing quality (total returned/total shipped). The query works fine
without the WHERE statement for all customers. But I want to filter it out
for a specific Account Manager's customer's only.

I tried the WHERE statement below, but it doesn't work.

WHERE (((qryQualityRGA.AcctmgrID)=getmyvariable()));

Interesting to me is using that last statement brings up a parameter box
asking for the value of qryQualityRGA.AcctmgrID????


SELECT qryQualityRGA.plant, qryQualityRGA.AcctmgrID,
qryQualityRGA.TransactionDate, qryQualityRGA.QuantityReturned,
qryQualityRGA.QuantityShipped FROM qryQualityRGA UNION ALL SELECT
qryQualityShipped.plant, qryQualityShipped.AcctmgrID,
qryQualityShipped.TransactionDate, qryQualityShipped.QuantityReturned,
qryQualityShipped.QuantityShipped FROM qryQualityShipped
WHERE (((qryQualityShipped.AcctmgrID)=getmyvariable()));

 

[Marshall Barton]

Below is a union query which gathers up shipment and returns to give data
representing quality (total returned/total shipped). The query works fine
without the WHERE statement for all customers. But I want to filter it out
for a specific Account Manager's customer's only.

I tried the WHERE statement below, but it doesn't work.

WHERE (((qryQualityRGA.AcctmgrID)=getmyvariable()));

Interesting to me is using that last statement brings up a parameter box
asking for the value of qryQualityRGA.AcctmgrID????


SELECT qryQualityRGA.plant, qryQualityRGA.AcctmgrID,
qryQualityRGA.TransactionDate, qryQualityRGA.QuantityReturned,
qryQualityRGA.QuantityShipped FROM qryQualityRGA UNION ALL SELECT
qryQualityShipped.plant, qryQualityShipped.AcctmgrID,
qryQualityShipped.TransactionDate, qryQualityShipped.QuantityReturned,
qryQualityShipped.QuantityShipped FROM qryQualityShipped
WHERE (((qryQualityShipped.AcctmgrID)=getmyvariable()));

Each SELECT statemet has a separate WHERE clause, not one
WHERE clause for all SELECT statements in the UNION.

In this case, I think you need to move it up before UNION