Nested IFs

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Folks can anyone answer a simple question for me?

Has anyone used in-line IF's much? If so, do you know when they terminate
when nested? Is it when the first condition is true or does it evaluate the
whole string? Ie:

IIF(x>0, z1, IIF(x+1>0, z2, IIF(x+2>0, z3, z4)))

If x = 1 will that IIF return z1 (stopping as soon as x>0 is true) or z3
(going all through to when x+2>0 is true) ?

Thanks for any help.
 
Andrew, can you think of a way to answer your own question here?

Perhaps you could open the Immediate Window (Ctr+G) and enter:
? IIF(1>0, 99, IIF(1+1>0, 999, IIF(1+2>0, 999, 9999)))

The crucial aspect of IIf() is that it is very inefficient. Although it
could return just the outmost true value, it calculates all values anyway.
 
I've run across this a couple of times. In VBA, it calculates all values whether it needs
them or not, in Jet it doesn't do this. So, you can get by with things in a query that you
can't get by with in VBA.

Example:

If x = 0 then in VBA this will fail with a divide by zero error.
Iif(x=0, 0, y/x)
However, in a query the above will work.
 
Back
Top