IIF, OR & Nz Formula Assist Please

liamrowan

Registered User.
Local time
Today, 18:11
Joined
Jul 15, 2010
Messages
54
I have two formulas that both work by themselves:

1. IIf(Nz([Approved])=0,0)
2. IIf(Nz([Allocated])+(Nz([Carryover]))=0,0)

But what I really need is to combine these formulas so that if either is true a zero is returned. Here is one combined version I tried:

IIf(Nz([Allocated])+(Nz([Carryover]))=0 OR (Nz([Approved])=0,0)))

I keep getting a "wrong number of arguments" message. I've tried many combinations of parentheses but can't find the fix. Can anyone tell me what's going wrong?

Thx, William
 
Try This:

Iif(Nz([Allocated])+(Nz([Carryover]) = 0,WhatYouWantToHappen,Iif(Nz([Approved]) = 0,Whatyouwanttohappen,WhatToDoIfNeitherIsZero))
 
Should be:

IIf(Nz([Allocated], 0) + Nz([Carryover], 0) = 0 OR Nz([Approved], 0) = 0, 0, Nz([Allocated],0) + Nz([Carryover],0))

I put in the allocated + carryover if they both are false but you can substitute what you want there. The key is that the IIF needs both what to set for the True and the False states.
 
Try this:

IIf(Nz([Allocated],0)+(Nz([Carryover],0))=0 OR (Nz([Approved],0)=0,0),0)
 
Try This:

Iif(Nz([Allocated])+(Nz([Carryover]) = 0,WhatYouWantToHappen,Iif(Nz([Approved]) = 0,Whatyouwanttohappen,WhatToDoIfNeitherIsZero))

Almost but not quite. You shouldn't put another IIF in there as it isn't required. You can use the OR like they had - just with a few modifications.
 
Almost but not quite. You shouldn't put another IIF in there as it isn't required. You can use the OR like they had - just with a few modifications.

I didn't know you could use OR like that, I've tried it in the past with no success. Thanks for the info. :)
 
Try this:

IIf(Nz([Allocated],0)+(Nz([Carryover],0))=0 OR (Nz([Approved],0)=0,0),0)

Parens in the wrong place (and no need for all of them) For your statement Rick it should be:

IIf(Nz([Allocated],0)+ Nz([Carryover],0)=0 OR Nz([Approved],0)=0,0,0)

But then you are putting 0 no matter what the condition is. :)
 
Thanks, everyone. The discussion enabled me to piece together the final formula that returned what was needed:

IIf(Nz([Allocated])+(Nz([Carryover]))=0,0,(Nz([Approved])/(Nz([Allocated])+(Nz([Carryover])))))

Wm
 

Users who are viewing this thread

Back
Top Bottom