Sum based on another field

[davidrsutton]

Hi - I've got a feeling that there is probably a very simple answer to this, but it's driving me mad at the moment so I'm hoping that someone can help...

I'm trying to write an expression in a query which will do the following for each record within a table called "records":

If the field "actual cost" is either 0 or is null then the expression should equal the number stored in the field "cost".

If the field "actual cost" is anything else, then the expression should equal the number stored in "actual cost"

I've tried using the following expression, but it's not working - maybe there is something obvious I've done wrong?

Sum(IIf([Actual Cost] Is Null,[Cost],[Actual Cost]))

I'm sure it must be dead simple really, so some help would be great.

Thanks in advance.

 

[mp9]

If you're doing this for every record, try this:

=IIf(Nz([Actual Cost],0)=0,[Cost],[Actual Cost])

http://www22.brinkster.com/accessory/

 

[lupins46]

Assuming the rest of your query is ok then just do this:

Sum(IIf(nz([Actual Cost],0) =0,[Cost],[Actual Cost]))