Help with IIF 1

[Boccle]

Hi All,

I am having a problem with a query.

I have a query that selects the following fields:
'WASTE_PROD'
'WASTE_CODE'
from a table 'tblWasteData'.
This query also creates a new field called 'WASTE_PROD2'.

The values for 'WASTE_PROD2' are equal to the values in field 'WWASTE_PROD' WHERE the 'WASTE_CODE' matches 'InventCodes' selected in a previous query.

Below is the SQL for my two queries:


qrySelectCodes

SELECT tblWasteTypes.InventCode, tblWasteTypes.WasteCat
FROM tblWasteTypes
WHERE (((tblWasteTypes.WasteCat)=[Forms]![frmRunCriteria]![cboWasteCat]));

The above query returns a number of InventCodes which it lists in field InventCode.

I would like the following query to put the value of WASTE_PROD into WASTE_PROD2 if WASTE_CODE matches any of the codes listed in field InventCode from the above query.
If there is no match then a '0' should be entered.

However, as the query stands, it asks for a value for qrySelectCodes!InventCode. If i type an appropriate value in, then the query runs. I think the problem is that I do not know how to say 'where WASTE_CODE equals ANY of the values in field InventCode in qrySelectCodes'

qrySelectRecords

SELECT tblWasteData.IND_ACT1, tblWasteData.WASTE_CODE, tblWasteData.WASTE_PROD, IIf([WASTE_CODE]=qrySelectRecords!InventCode,[WASTE_PROD],0) AS WASTE_PROD2
FROM tblWasteData;

I hope this makes sense!

Any help would be greatly appreciated.

 

[ssecca]

SELECT tblWasteData.IND_ACT1, tblWasteData.WASTE_CODE, tblWasteData.WASTE_PROD, IIf([WASTE_CODE]IN
(
SELECT tblWasteTypes.InventCode
FROM tblWasteTypes
WHERE (((tblWasteTypes.WasteCat)=[Forms]![frmRunCriteria]![cboWasteCat]))
),[WASTE_PROD],0) AS WASTE_PROD2
FROM tblWasteData;


I think (it has been a while) that this is the answer. You may need a little tweaking.
Good Luck
ssecca

 

[Boccle]

Thanks........that works fine