Safely Using TempVars in Queries

Calling TempVars directly from a query can cause issues. Instead, use this simple wrapper function to save yourself some potential headaches.

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    :
' 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:
' ----------------------------------------------------------------
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.


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:

Image by MetsikGarden from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0