Expressions vs. Code
There are several ways to build program logic into a Microsoft Access application. The three most common ways are macros, expressions, and VBA code.
I recognized early on when I first started developing in Access that expression evaluation in queries was similar but different than code execution in VBA. However, it took me awhile to recognize that expression evaluation was happening pretty much any time that I was not writing code directly in the IDE.
Macros
Macros are abhorrent. They don't support comments. They're difficult to troubleshoot. There's no good way to track how and where they're being used. I guess they're supposed to be easier for beginners to get started with because it's a more visual tool that avoids dealing with syntax issues? I don't really know.
In 13+ years of writing Access applications, I have used no more than two macros in any project. Those macros are AutoExec and AutoKeys. I only use those because there's no other way to get the functionality they provide. I shall write no more about macros, because I strongly advise you to avoid them entirely (notwithstanding these two exceptions).
VBA Code
This is the text that we write into the Visual Basic for Applications integrated development environment (VBA IDE). It gets compiled either on-demand or explicitly, via the Debug -> Compile menu command. The code then gets executed according to the VBA Language Specification.
Included in the specification are the routines that comprise the standard library, such as IIf() (Immediate If):
IIf always evaluates both TruePart (first) and FalsePart, even though it returns only one of them. For example, if evaluating FalsePart results in a division by zero error, an error occurs even if Expression is True.
Expressions
Expressions are basically code statements that are evaluated outside of the VBA IDE, such as within queries, in form/report/control properties, or via the Access-specific Eval() function. These expressions are evaluated via the Microsoft Access database engine Expression Service (ACEES.DLL).
Testing to determine the environment
If you're not sure how your code is being executed, you can use the following test function along with IIf():
Function Watch(Val, Optional CalledFrom As String = "")
Debug.Print Val, CalledFrom
Watch = Val
End Function
Why IIf()? Because it behaves differently depending on whether it's the function being executed in VBA or the statement being evaluated by the expression service. In VBA, both the True and False parts of the IIf() function are evaluated regardless of the test condition. However, the IIf() statement executes either the True part or the False part, but never both.
The Watch() function simply gives us an easy way to observe this behavior.
Queries
First, create and run a query with the following SQL:
SELECT IIf(True, Watch("True", "SQL"), Watch("False", "SQL"))
Form/Report/Control Properties
Next, create a blank form, add a button with the following OnClick property, show the form, and click the button:
=IIf(True,Watch("True","Form"),Watch("False","Form"))
Macros
Next, create a new macro, Add New Action: RunCode, enter the following for "Function Name", then save and run the macro:
IIf(True, Watch("True", "Macro"), Watch("False", "Macro"))
Eval() function
The Access.Application object has an Eval() method that can be used to evaluate string expressions. To test this, go to the Immediate Window and execute the following function after the results in the window:
?Eval("IIf(True, Watch('True', 'Eval'), Watch('False', 'Eval'))")
VBA Code
Finally, execute the following line of code at the bottom of the immediate window:
IIf True, Watch("True", "VBA"), Watch("False", "VBA")
Results
If you did everything correctly, you should have the following result in the immediate window:
True SQL
True Form
True Macro
?Eval("IIf(True, Watch('True', 'Eval'), Watch('False', 'Eval'))")
True Eval
IIf True, Watch("True", "VBA"), Watch("False", "VBA")
True VBA
False VBA
In other words, only the "VBA Code" sample gets executed by Visual Basic for Applications. The Access database engine Expression Service evaluates all the other samples.
Implications
Why does this matter? For one thing, there are statements that the Expression Service will evaluate that VBA will not. In particular, the Expression Service supports the IS [NOT] NULL statement, whereas VBA only has the IsNull() function.
The Expression Service will call back into VBA to execute public Functions, but this is less efficient than using statements the Expression Service implements natively. In other words, if you can use IS [NOT] NULL then you probably should use it instead of the IsNull() function.
Depending on the query or continuous form, there can be a noticeable difference in performance between using Nz(LastName, "Unknown") and IIf(LastName Is Null, "Unknown", LastName). While the former is faster to write, the latter yields better performance. The IIf() approach also maintains the original data type of our LastName field, whereas Nz() returns a variant which is usually not what we want.
References?
So where is the definitive reference on the Access database engine Expression Service? Where can I find official documentation that explains how the IIf() statement behaves? No, really, that's not a rhetorical question. I can't find anything that documents this behavior anywhere online.
The closest thing I have found is the Microsoft Access desktop database SQL Reference. However, that help section has no mention of the differing behavior of the IIf() statement. There is a page on SQL Expressions with this little nugget:
In addition, the VBA expression service offers over 100 VBA functions that you can use in SQL expressions.
That's great that we can use over 100 VBA functions. But, um, which 100 functions? Wouldn't you like to know ;-). Wouldn't we all like to know.
Bottom Line
How your code executes depends on where it executes. While the distinction doesn't matter 99% of the time, it is important to understand the difference so you don't get bitten by the 1% of the time that it does matter.
Image by Peter Dargatz from Pixabay