1. Registered User
Join Date
Nov 2008
Posts
26

Here is my current iif statement:

Code:
`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?

2. Registered User
Join Date
May 2009
Posts
258
Use the Nz function:
Code:
`Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,Nz([Cost],0),0)))`
You should probably further condense it to one IIf expression:
Code:
`Sum(IIf([Operation]=854 And [DStamp]<>#1/26/1986#,Nz([Cost],0),0))`
Regards,

Ax

3. Registered User
Join Date
Nov 2008
Posts
26
Originally Posted by Ax238
Use the Nz function:
Code:
`Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,Nz([Cost],0),0)))`
You should probably further condense it to one IIf expression:
Code:
`Sum(IIf([Operation]=854 And [DStamp]<>#1/26/1986#,Nz([Cost],0),0))`
Regards,

Ax
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?
---------------
Last edited by reeser; 08-31-09 at 14:27.

4. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
You can't use And like that in an expression Ax.

CCCost: Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,[Cost],0),0))

And just to be sure:

CCCost: Nz(Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,[Cost],0),0)),0)

5. Registered User
Join Date
May 2009
Posts
258
Really? Hmmm, well I've been using it like that for quite a few years and it seems to work just fine. You're telling me that a boolean operator is not valid in a boolean expression?

BTW:
Originally Posted by reeser

6. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
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

Thanks

7. Registered User
Join Date
May 2009
Posts
258
Please do let me know what you find out.

8. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Tested. You are correct! Thank you!!!! You've made my day!

I have been avoiding using AND in such expressions for years, for reasons I no longer remember! No mind as those reasons were obviously not valid!

This should make things easier ^^

Thanks again Ax

9. Registered User
Join Date
May 2009
Posts
258

10. L33t Helpa Munky
Join Date
Nov 2007
Location