Beware TempVars in Queries

As a personal preference, I've always favored global form controls over TempVars as an alternative to global variables in Access applications.  In the article I just linked, I included a short reason for why I prefer a global form to TempVars:

The TempVars object suffers from the same disadvantages as the hidden form approach and lacks many of the other benefits I describe herein.

I always thought that query support was one of the benefits of using a hidden global form rather than TempVars.  However, several people have responded that query support is something they believe to be one of the major advantages to using TempVars.

I decided to see what all the hype was about.  What I found shocked me.

Numbers...or Character Codes?

I started out with a simple test.  

I created a TempVar named LongVar and set its value to 42.  To ensure it was being passed as a long integer, I even wrapped it in the CLng() function:

I then used it as the field value in a query.  To my surprise, the field displayed an asterisk (*) instead of the number 42.  The only way I could get it to display 42 was to apply the CLng() function in the query itself:

I could not make sense of the asterisk at first.  Through some trial and error, though, I realized that 42 is the ASCII character code for the asterisk:

As an alternative to using the CLng() function, I decided to try explicitly returning the Value property of the TempVar in question.  Unfortunately, this did not work.  The query still shows an asterisk instead of the value 42:

Character Codes or ... Whatever This Is?

It turns out that not all data types are created (or treated) equally when it comes to how Access displays values on query datasheets.

For this series of tests, I used the same value–42–but I converted it to Currency when setting the TempVar:

When I tried to return the value directly in the query (i.e., "MoneyVarBare"), it returned what appeared to be a couple of Unicode characters.  If I force the value to be a Currency via CCur(), then the original numeric value of 42 is returned ("MoneyVarCCur").

Even the String is Not a String

Finally, I tried assigning 42 as a String to a TempVar named StringVar.

Interestingly, even this value appeared differently when shown directly vs. when converted to a String via CStr():

Notice that when calling the TempVar directly ("StringVarBare"), the resulting value is right-aligned in the datasheet view.  This right-alignment suggests that the value is being treated as a number rather than a string.

When we apply the CStr() function to the TempVar ("StringVarCStr"), the resulting value is left-aligned.  This implies that the "StringVarCStr" value is being treated as a string rather than a number.

Unusual Usage?

Now, I think it's rather unusual to return a TempVar value as a standalone field value.  In most cases, the TempVar will be used as part of the query's criteria (i.e., the WHERE clause).

I did not test the use of TempVars in the WHERE clause for this article.  It's possible–probable, even–that TempVar values work just fine as part of a WHERE clause.  However, now that I know of this odd behavior in the SELECT clause, I'm not sure I would ever trust TempVars enough to use them directly in a query in any capacity.

I'll continue to use my hidden global form controls, thank you very much.

Referenced articles

The Global Form

Storing global variables on a hidden form has some distinct advantages over storing them in VBA.

No Longer SetMike Wolfe

Image by Sam Williams from Pixabay