In a previous article, I wrote about the differences between expression evaluation and code execution:
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).
In that article, I pointed out that there are differences between the two environments. For example, the Expression Service has a short-circuiting IIf statement, whereas VBA has an IIf function. The distinction is that the VBA IIf function always evaluates both the True and the False parts, while the Expression Service IIf statement executes only the True or the False part (based on the result of the conditional).
Implicit Type Conversion
Another key difference is the way the two environments perform implicit type conversion. In the Expression Service, all whole number literals are implicitly treated as
Long values. However, in VBA, any whole number that fits within the range of the
Integer type is treated as an
To prove this, we can use the Eval() function to force our code to be run through the Access database engine Expression Service. See for yourself:
Adventures in Rounding
Things get even more interesting when we start dealing with non-whole numbers. VBA treats all decimal numbers as the
Double data type. The expression service, meanwhile, treats all decimal numbers as the
Decimal data type.
Decimal may appear similar, they have very different implementations behind the scenes. The Double data type performs binary floating-point operations, while the Decimal data type performs integer operations with a shifted decimal point.
This is an important distinction, as certain base-10 decimals cannot be exactly represented by a base-2 (i.e., binary) system.
Why does this matter in practical terms? Because an operation executed in VBA code can return a different result than the exact same operation evaluated using the expression service. See below:
Here are those same results in text form in case you want to copy and paste these numbers to play around with them yourself:
? 1.499999999999999 1.5 ?Eval("1.499999999999999") 1.499999999999999 ? Round(1.4999999999999999) 2 ?Eval("Round(1.4999999999999999)") 1
Double vs. Long?
One final interesting difference is in how the two execution environments implicitly convert whole numbers with a trailing
What about you? What differences have you observed between the database engine expression service environment and the standard VBA code environment? Let me know in the comments below.