CC Cost: Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,[Cost],0)))
It returns the [Cost] field amount correctly but if data doesn't match the criteria, it will return a blank. What can I change so that if no Cost is applicable, then a 0 value will be returned in the query?
You should probably further condense it to one IIf expression:
Sum(IIf([Operation]=854 And [DStamp]<>#1/26/1986#,Nz([Cost],0),0))
It still returns a blank. There is a good chance that for a particular record, 854 won't exist at all. It could go from 850 to 857 more likely. Is that fact it wouldn't find that value causing it to return the blank?
NM...I had updated the statement wrong. Your solution works perfectly. Thanks
Interesting... my current understanding has been that you can't use such things in the IIf function. Of course it works on many other things, but yeah, I am still under the impression that it doesn't work in an IIf. Must try it out next time I am coding