The IIf() Function vs. The IIf() Statement
The IIf() function and the IIf() statement are fundamentally different animals.
Unfortunately, they are nearly impossible to tell apart. And yet, it is important to be able to do so. Why?
Because this IIf() function is a runtime error waiting to happen:
IIf(Attempts = 0, 0, Successes / Attempts)While this IIf() statement will safely return a value even if Attempts = 0:
IIf(Attempts = 0, 0, Successes / Attempts)So, can you spot the difference between the two?
Telling Them Apart
If you can't spot the difference between the two samples above, that's because there is no difference.
The IIf() function looks identical to the IIf() statement.
The difference is in how–or more precisely, where–they get evaluated.
- The IIf function is a member of the VBA.Interaction standard library.
- The IIf statement gets evaluated by the Jet/ACE database engine's Expression Service (acees.dll).
I wrote about the differences between code and expressions here:
The Fundamental Difference Between the IIf() Function and the IIf() Statement
Three words: short-circuit evaluation.
- The IIf statement has it.
- The IIf function does not.
What is Short-Circuit Evaluation?
In short-circuit evaluation, the code evaluates only those expressions necessary to determine the result.
The IIf statement and IIf function both take three arguments:
- expr: a boolean condition
- truepart: the result to return if expr is True
- falsepart: the result to return if expr is False
With the IIf statement, only two of those arguments are ever evaluated: the expr and–depending on the result of the expr–either the truepart OR the falsepart.
With the IIf function, all three arguments MUST be evaluated before they even get passed to the function.
Why It Matters
Let's go back to the original example code:
IIf(Attempts = 0, 0, Successes / Attempts)What happens if the value of Attempts is zero?
IIf statement
- The expr evaluates to True.
- The truepart evaluates to
0. - The statement returns
0.
IIf function
- The expr evaluates to True.
- The truepart evaluates to
0. - The falsepart evaluates to #ERROR#: Division by zero
The function version returns an error because it was forced to evaluate both the truepart and the falsepart expressions.
Where is IIf Treated as a Statement?
IIf is evaluated as a statement in the following places:
- Queries
- Form/Report/Control Properties (e.g., a TextBox ControlSource)
- The Access Application.Eval() function
For more details–including how to definitively test whether IIf is being treated as a statement or a function–read my article on the differences between expressions and code: Expressions vs. Code.