Trouble with a union Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tsison7 - 21 Jan 2008 06:49 GMT

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()));

Signature

TIA

Reply to this Message

Tom Wickerath - 21 Jan 2008 07:36 GMT

What result do you see if you open the Immediate Window (use <Ctrl><G>) and
enter the following command:

? getmyvariable

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

> Below is a union query which gathers up shipment and returns to give data
> representing quality (total returned/total shipped). The query works fine
[quoted text clipped - 15 lines]
> qryQualityShipped.QuantityShipped FROM qryQualityShipped
> WHERE (((qryQualityShipped.AcctmgrID)=getmyvariable()));

Reply to this Message

John W. Vinson - 21 Jan 2008 17:17 GMT

>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.

What "doesn't work"? Did you include the criterion in BOTH SELECT parts or the
UNION query? They're independent, you need the criterion twice.

John W. Vinson [MVP]

Reply to this Message