Microsoft Access: Datatypes and Switch vs Iff program flow statements

I think this may be the most obscure blog post I’ve ever written. I only write it because, even after I knew the answer, I couldn’t find this documented anywhere. Perhaps this will help someone searching for an answer someday.

I’ve tested this in Access 2003, but not Access 2007 (a disastrous upgrade btw).

If you’re actually reading this, you are using Microsoft Access and you have been using the "Switch” “Program Flow Built-In Function”. This is a unique to Access hack that allows one to mix a bit of programming logic into a query. In the pile of hacks that make up Microsoft Access this one’s a bit of a gem. You can mangle quite a bit of data with it. I believe it is a call out to Access Basic.

You used the Switch statement with a numeric data type. When you were done you attempted to do something with the result, such as a Join on another numeric. You then saw a “Type mismatch in expression.” error message.

It’s not documented in Access, but the Switch statement forces the result to be a string. If you don’t want to change data types to a string you should use nested IIF statements in place of the Switch statement.

You can also wrap the Switch statement in another function that casts the string to a numeric value.

That is all.

PS.

As long as you’ve read this far, here are some of my personal notes on the Switch statement…

Access has 3 Program Flow built-in functions: Choose, IIf and Switch. I use IIf and Switch.

These are taken from Access Basic. They can be used in Access Basic functions, or they can be used as functions in a column name (where I use them).

Switch has the advantage that it can handle multiple conditions cleanly, and include an error statement. It is poorly documented (see below) [1]. It can be difficult to construct a complex Switch in the Access editor or function wizard, it's better to construct it in a text editor and paste it in.

Switch can use the True and False operators. So if you want to have an error result used when all matches fail, you set "expr-n" to the operator "True" (no quotes). You might think  you should put the most common condition first to reduce execution time, but Switch always evaluates all the expressions.

Example of use of Switch (in this case the test would never be reached, Null and Not Null covers all branches):

SNAP_LOCAL_CONCEPT_TYPE: Switch([RESULT_UNITS] Is Null,"NumericObservationWithoutUnits",[RESULT_UNITS] Is Not Null,"NumericObservationWithUnits",True,"ERROR: IMPOSSIBLE")

[1] From Access Help ...

Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding expression is True.

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

Switch returns a Null value if:

• None of the expressions is True.
• The first True expression has a corresponding value that is Null.

Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.