Nested Query

[livvie]

Hi
I want to run a query that returns a calculated field (x*y) which I have done but at the same time I want to do (x*y) for the records that are marked with a flag producing two figures so that I can do two subtotals ? Any ideas. I am doing this on a form using Queries and the subtotals in the footer. Here is what I have so far :

SELECT Matid, est_id, mat_desc, mat_qty, mat_units, mat_cost, switch1, hook1, hook2, mat_spec, buyfrom, External, Delivery, mat_qty * mat_cost AS Costs,

FROM tblctmat

 

[PHV]

Take a look at the Iif function

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[livvie]

But that only returns one of the values I need both.

 

[lespaul]

then you can do a nested iif statement:

iif(condition, value if true, value if false)

iif(a = 2, b = 3, iif(c = 4, d=6, iif(e = 7, f= 8, 'no more')))

so if a = 2 then b = 3 if a <> 2 then check if c = 4 if it is then d = 6, if c <> 4 then check if e = 7, if it is then f = 8, if e <> 7 then print 'No More'

Leslie