Hidden Features

Expressions vs. Code: Implicit Type Conversion

In Microsoft Access, is a whole number literal implicitly treated as a Long or an Integer or a Double? As it turns out, it depends on where you ask.

Mike Wolfe

Sep 7, 2021 • 3 min read

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 Integer.

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.

While Double and 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 .0 decimal.

Other Quirks

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.