adding a column to query

[Boccle]

Hi Everyone,

I'm relatively new to Access 2000 and have been struggling with this one for a while.

I have a table tblWasteData with the following fields:
INDUSTRY_CODE
WASTE_CODE
WASTE_PROD


I have a query that currently picks out records from tblWasteData where INDUSTRY_CODE matches data in a text box (txtIndCode) on a form.

The results from this query will show records relating to one industry type only. However, WASTE_CODE varies for these records.

I would like to run the query so that it does the above and also does the following:
* creates a new column WASTE_PROD2 (the values in this column will be used later in another query to perform a calculation)
* the values in this column will equal those in WASTE_PROD if WASTE_CODE equals data in cboWasteCode on the form. If WASTE_PROD does NOT equal the value in cboWasteCode then a zero "0" should be entered into WASTE_PROD2.

My SQL so far:

SELECT tblWasteData.INDUSTRY_CODE, tblWasteData.WASTE_CODE, tblWasteData.WASTE_PROD
FROM tblWasteData
WHERE tblWasteData.INDUSTRY_CODE = Forms!MyForm!txtIndCode;


I hope someone can help....

TIA

 

[RivetHed]

Hiya Boccle,

If you go into the expression builder for a fresh field and enter:
Iif([WASTE_CODE]=[Forms]![MyForm]![cboWasteCode],[WASTE_CODE],0)
you should get a fresh column with the data you required.

Hope this helps you

 

[Boccle]

Ummm...I'm afraid that doesn't seem to work.

I get a message saying "The expression is typed incorrectly or is too complex to be evaluated. For example, a numeric expresssion may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

Any ideas why it came up with that???

I can create a new column called WASTE4CALC which contains all the values in the field WASTE_PROD (see code below) but I just can't manage to make the new column only display the values from WASTE_PROD if that record has a WASTE_CODE that matches the waste code in cboWasteCode

SELECT tblWasteData.INDUSTRY_CODE, tblWasteData. WASTE_CODE, tblWasteData.WATE_PROD, [WASTE_PROD] As WASTE4CALC
FROM tblWasteData;

 

[RivetHed]

seems strange,

you've got me interested now, let me quickly knock up a duplicate and get back to you (I'm probably missing something obvious)

 

[RivetHed]

I made a little typo in my first posting should read
Iif([WASTE_CODE]=[Forms]![MyForm]![cboWasteCode],[WASTE_prod],0)

instead of

Iif([WASTE_CODE]=[Forms]![MyForm]![cboWasteCode],[WASTE_CODE],0)

however I have no problem with this when i tried it on my machine here is my SQL

SELECT tblWasteData.Industry_code, tblWasteData.waste_code, tblWasteData.waste_prod, IIf([waste_code]=[forms]![MyForm]![cbowastecode],[waste_prod],0) AS Waste_Prod2
FROM tblWasteData
WHERE (((tblWasteData.Industry_code)=[forms]![MyForm]![txtindcode]));

try that, if you still have problems I'll have another look

 

[Boccle]

Horay it works!

Thanks

 

[RivetHed]

no probs : p