> ## Content Index > Fetch the complete content index at: https://nolongerset.com/llms.txt > Use this file to discover other available public pages before exploring further. # Expressions vs. Code - URL: https://nolongerset.com/expressions-vs-code/ - Published: 2020-10-03T22:41:44.000Z - Updated: 2026-05-08T13:08:33.000Z - Description: When is code not code? When it's an expression. What's the difference and who really cares? Let's explore. - Author: Mike Wolfe - Tags: Hidden Features, #Import 2026-05-20 02:59 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](https://support.microsoft.com/en-us/office/create-a-macro-that-runs-when-you-open-a-database-98ba1508-dcc6-4e0f-9698-a4755e548124)** and **[AutoKeys](https://support.microsoft.com/en-us/office/run-an-access-macro-by-using-a-keyboard-shortcut-0d267570-d3da-46eb-9bfb-a3c81af676be)**. 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](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/MS-VBAL/d5418146-0bd2-45eb-9c7a-fd9502722c74). Included in the specification are the routines that comprise the [standard library](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/MS-VBAL/c645c903-9bd4-4849-8735-3136e867536a), such as `[IIf()](https://docs.microsoft.com/en-us/openspecs/microsoft%5Fgeneral%5Fpurpose%5Fprogramming%5Flanguages/MS-VBAL/3593c073-c770-4060-8416-aaf7545ea5fe)` (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()`: ```vba 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: ```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](https://docs.microsoft.com/en-us/office/vba/api/access.application.eval) 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](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/microsoft-access-sql-reference). However, that help section has no mention of the differing behavior of the **IIf()** statement. There is a page on [SQL Expressions](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/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](https://pixabay.com/users/peterdargatz-5783/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=173940) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=173940)*