Safely Using TempVars in Queries

This article is part of the series, A Safer Way to Use TempVars.

TempVars are a handy alternative to global variables in Microsoft Access, but they have some quirks.

Among those quirks is that if you directly display a numeric TempVar as a field in a query, it actually treats the TempVar value as a character code and shows the corresponding character rather than the numeric value:

I wrote about the issue here: Beware TempVars in Queries.

Luckily, the solution is very simple: create a wrapper function and call that instead of calling TempVars directly.

The TempVars Wrapper Function

' ----------------------------------------------------------------
' Procedure : GetTempVar
' Date      : 9/21/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/gettempvar/
' Purpose   : A public function to return the value of a TempVar object
'               that can be called from a query or form/report control
' Notes     - Returns Null if the TempVar does not exist (rather than raise an error).
'           - This function provides no type safety or compile-time checking.
'           - Use the TV class module to get these benefits when
'               interacting with TempVars from VBA; for more info,
'               see: https://nolongerset.com/tv-class/
' ----------------------------------------------------------------
Public Function GetTempVar(VarName As String) As Variant
    GetTempVar = TempVars(VarName).Value
End Function

To use this function, you pass the name of the TempVar as a string.

Note that the function will not throw an error if the TempVar does not exist; it will merely return Null.

Usage

Here's how the function would be used in a query:

It's worth noting that this function returns a Variant, rather than a more explicit value type.  You can see this distinction in the screenshot above, as Variant values get left-aligned in Access query results while numeric values (Long, Integer, etc.) are right-aligned.  

Returning a Variant is appropriate insofar as all TempVar values are Variants, but it does mean that the function is less type-safe than my TempVars TV class module approach.  For that reason, and several others, I recommend you use the TV class module wherever possible.

I recommend you only use this function in places where the class module is not accessible:

• Queries
• Form/report controls
• Pretty much anything being evaluated by the Jet/ACE expression service

Image by MetsikGarden from Pixabay