Using the Switch Function in Microsoft Access

By Patrick G. Matthews

Introduction

In Microsoft Access, Switch is a very useful and powerful function that allows you to build conditional branching logic into your expressions in queries, forms, and reports.  In essence, you pass Switch one or more pairs of expressions; within each pair, the first expression is evaluated as a boolean expression, and the second is evaluated for its result.  The Switch function evaluates each pair of expressions, and returns the result corresponding to the first boolean expression in the list that evaluates to True.

Switch is often compared to the CASE statement in SQL Server, or the DECODE function in Oracle.

In my opinion, Switch is an underused function, and a better alternative to the somewhat more common "nested IIf" expressions often used in logical expressions requiring more than two branches.

This article provides an introduction to the Switch function, with sections that:

Switch Syntax

The basic structure of a Switch expression is as follows:

Switch(expression1, value1[, expression2, value2 ... [, expressionN, valueN]])

The Switch function evaluates each pair of expressions, and returns the value associated with the first expression that evaluates to True.

Further notes:

For example suppose we have a table of prospects, with a due date for a follow-up call, and we need an ad hoc analysis rating prospects by due date status:

For this analysis, one could use Switch in a query to indicate status:

SELECT ProspectName, FollowupDate, 
                          Switch(FollowupDate - Date() < -5, "Very Overdue", FollowupDate - Date() < 0, "Overdue",
                          FollowupDate - Date() < 6, "OK", True, "Future") AS Status
                      FROM Prospects
                      ORDER BY FollowupDate, ProspectName
                      

Select all Open in new window

Alternatives in the Access User Interface: IIf and Choose

In the Access UI--that is, in queries, forms, and/or reports--the chief alternatives to Switch for logical branching in formulas are the IIf and Choose functions.

Consider the example above, in which we assigned due date status to prospects.  Instead of using Switch, we could have used a series of nested IIf expressions, as follows:

SELECT ProspectName, FollowupDate, 
                          IIf(FollowupDate - Date() < -5, "Very Overdue", IIf(FollowupDate - Date() < 0, "Overdue", 
                          IIf(FollowupDate - Date() < 6, "OK", "Future"))) AS Status
                      FROM Prospects
                      ORDER BY FollowupDate, ProspectName
                      

Select all Open in new window

I tend to find multiple nested IIf expressions difficult to understand, so as a matter of personal taste, I usually prefer to use Switch instead of nested IIf.  Indeed, for a long time I never knew Switch existed: I was an Excel user first, and there is no analogous function to Switch in Excel.  There is, of course, an IF function in Excel.

Consider a different, if somewhat silly example: in a query, based on the month for a particular date, add a label indicating the expected temperature (think temperate climate in the Northern Hemisphere):

SELECT SomeDate, 
                          Switch(Month(SomeDate) = 1, "Cold", Month(SomeDate) = 2, "Cold", Month(SomeDate) = 3, "Cold",
                          Month(SomeDate) = 4, "Mild", Month(SomeDate) = 5, "Mild", Month(SomeDate) = 6, "Hot",
                          Month(SomeDate) = 7, "Hot", Month(SomeDate) = 8, "Hot", Month(SomeDate) = 9, "Hot",
                          Month(SomeDate) = 10, "Mild", Month(SomeDate) = 11, "Cold", True, "Cold") AS ExpTemp
                      FROM SomeTable
                      

Select all Open in new window

In this case, since the criteria being evaluated are all consecutive integers starting with 1, and having a definite end point, the Choose function may be a viable alternative.  For example, rewriting the query above to use Choose:

SELECT SomeDate, Choose(Month(SomeDate), "Cold", "Cold", "Cold", "Mild", "Mild", "Hot", "Hot", 
                          "Hot", "Hot", "Mild", "Cold", "Cold") AS ExpTemp
                      FROM SomeTable
                      

Select all Open in new window

Alternatives in VBA

The Switch function is implemented in Visual Basic for Applications (VBA), and thus you can use Switch in VBA code for your Access application.  However, I do not recommend this: in my opinion, it is better to use the more formal flow control structures available in VBA.

Adapting the due date status example above into VBA results in:

Status = Switch(FollowupDate - Date < -5, "Very Overdue", _
                          FollowupDate - Date < 0, "Overdue", FollowupDate - Date < 6, "OK", _
                          True, "Future")
                      

Select all Open in new window

Instead of doing it that way, I recommended using If...ElseIf or Select Case, as follows:

'Using If...ElseIf
                      If FollowupDate - Date < -5 Then
                          Status = "Very Overdue"
                      ElseIf FollowupDate - Date < 0 Then
                          Status = "Overdue"
                      ElseIf FollowupDate - Date < 6 Then
                          Status = "OK"
                      Else
                          Status = "Future"
                      End If
                       
                      'Using Select Case
                      Select Case FollowupDate - Date
                          Case Is < -5: Status = "Very Overdue"
                          Case Is < 0: Status = "Overdue"
                          Case Is < 6: Status = "OK"
                          Case Else: Status = "Future"
                      End Select
                      

Select all Open in new window

Using these formal flow control structures results in, in my opinion, more readable and self-documenting code, and also allows you greater control over performance: in Switch, all expressions are always evaluated, whereas in the flow control structures above irrelevant expressions are not evaluated.

Example 1: Using Switch to Create Custom Sort Order

In her question Sorting in Reports, EE Member kristenbednarz wrote:

I want to sort a report based on criteria in a Query.

If the Text in the sorting column of the Query is either Beginning, Middle, End, Other or a blank space.  How can I sort the Report in Ascending chronological order: Beginning, Middle, End, Other, blank space as opposed to in Ascending in alphabetical order: blank space, Beginning, End, Middle, Other which is how it is now?

Does this make sense?  I am lost!

This is a fairly common requirement: we have a descriptor that a human normally would have little trouble "sorting", but the order comes out all wrong if we try to sort on it because the semantic order is not the same as the text-based order.

To answer this question, I created a sample file:

Q-26285482.mdb

In that file, I demonstrated two approaches for achieving the end result.  One approach used the Switch function to evaluate the Phase of a given project, and based on the text description of that phase return a number used to force a custom sort order.  The query for that approach was:

SELECT Switch([Phase] = "Beginning", 1, [Phase] = "Middle", 2, [Phase] = "End", 3,
                          [Phase] = "Other", 4, True, 5) AS SortOrder, Phase, Project, Hours
                      FROM tblProjects
                      ORDER BY Switch([Phase] = "Beginning", 1, [Phase] = "Middle", 2, [Phase] = "End", 3,
                          [Phase] = "Other", 4, True, 5);
                      

Select all Open in new window

Of course, while that worked, the approach I championed in that question involved creating a table to define the sort order.  For example, adding a table like this:

tblChronological
                      
                      Phase       | SortOrder
                      ------------| ---------
                      Beginning   |         1
                      Middle      |         2
                      End         |         3
                      Other       |         4
                      

Select all Open in new window

creates what is, in my opinion, a more sustainable and extensible approach.  I demonstrate both methods, from sample data table, to queries, and then reports, in the attached sample file above.

Example 2: Evaluating Multiple Conditions with Denormalized Data

While it is usually best to normalize your data, occasionally this is not possible, and we must make do with what we have as best we can.  In another recent question, SteveL13 asked:

This would be a simple solution IF the table I'm working with had been designed differently.  But here goes...

I have a query that gets data from a table that has fields named "Date 1", "Drop 1 Billed", "Date 2", "Drop 2 Billed", "Date 3", "Drop 3 Billed", "Date 4", "Drop 4 Billed", "Date 5", "Drop 5 Billed", and "Date 6", "Drop 6 Billed".  I need to add a field to this query to show the last date where the associated "billed" date is true.

What would be syntax be for this last column?

The Asker provided this screenshot of one line of the query results:


In this case, as long as we could assume that:

I was able to construct a Switch expression that evaluated each condition in turn, and return the appropriate date:

SELECT LogicJobN, Date1, Drop1Billed, Date2, Drop2Billed, Date3, Drop3Billed, 
                          Date4, Drop4Billed, Date5, Drop5Billed, Date6, Drop6Billed, 
                          Switch(Drop6Billed, Date6, Drop5Billed, Date5, Drop4Billed, Date4, Drop3Billed, Date3, 
                          Drop2Billed, Date2, Drop1Billed, Date1, True, Null) AS LastDate
                      FROM tblProject
                      

Select all Open in new window

Note the following about this solution:

Of course, as the Asker acknowledged, had the database been designed a little differently, using a more normalized model, this problem might have been easier to solve, and we may not have needed Switch at all.

Example 3: Using Switch to Return First Non-Null Value

In the last example, ProdOps needed a query that would resolve managerial assignments with multiple overrides:

I need to assign the correct Manager to a table of sales data in MS-Access.  The assignment is based on multiple criteria and I would appreciate your help with the correct SQL syntax.

The first criterion is matching the record based on the STATE where the transaction was sold.
The second criteria will override the first and is based on an Overlay rep being the selling rep - they can sell in any State.
The third criteria will override the first and the second where the selling rep is the manager themselves.
If no match can be found from any lookup then the Manager is blank or "None" is acceptable.

The Asker included a sample file, which included several tables relevant to the problem (see below):

Example-3.mdb


To determine the correct manager assignment, I employed a series of outer joins from Tbl_Data to the Tbl_ST, Tbl_Mgr, and Tbl_Overlay tables.  The outer joins were all necessary because the question stated the possibility that none of the lookups would find a match.

If I were doing this in SQL Server, I would probably use the COALESCE function, which returns the first non-null value in an arbitrary list.  The query might look something like this:

SELECT d.ID, d.Rep, d.Cust, d.ST, d.Amt, 
                          COALESCE([m].[Mgr], [o].[Mgr], [s].[Mgr], '<No match!>') AS Manager, 
                          CASE WHEN [m].[Mgr] IS NOT NULL THEN 'Manager override'
                              WHEN [o].[Mgr] IS NOT NULL THEN 'Overlay'
                              WHEN [s].[Mgr] IS NOT NULL THEN 'State'
                              ELSE '<No match!>' END AS Reason
                      FROM Tbl_Data AS d LEFT JOIN 
                          Tbl_ST AS s ON d.ST = s.State LEFT JOIN 
                          Tbl_Mgr AS m ON d.Rep = m.Mgr LEFT JOIN 
                          Tbl_Overlay AS o ON d.Rep = o.Rep;
                      

Select all Open in new window

Note how I used the highest priority override--that the Rep is in fact a manager--first, and then worked backward in decreasing priority, including a "no match" result if no matches were found.

Access, of course, has neither the COALESCE function nor the CASE statement, but in Access we can use Switch to provide the same functionality.  In the query below, the first Switch expression emulates COALESCE to find the manager assignment (or indicate that no matches were found), and the second Switch expression emulates the CASE structure above to return the "reason" for the manager assignment:

SELECT d.ID, d.Rep, d.Cust, d.ST, d.Amt, 
                          Switch([m].[Mgr] Is Not Null, [m].[Mgr], [o].[Mgr] Is Not Null, [o].[Mgr],
                          [s].[Mgr] Is Not Null, [s].[Mgr], True, "<No match!>") AS Manager, 
                          Switch([m].[Mgr] Is Not Null, "Manager override", [o].[Mgr] Is Not Null, "Overlay",
                          [s].[Mgr] Is Not Null, "State", True, "<No match!>") AS Reason
                      FROM ((Tbl_Data AS d LEFT JOIN 
                          Tbl_ST AS s ON d.ST = s.State) LEFT JOIN 
                          Tbl_Mgr AS m ON d.Rep = m.Mgr) LEFT JOIN 
                          Tbl_Overlay AS o ON d.Rep = o.Rep;
                      

Select all Open in new window

By successively testing for "[Column] Is Not Null", we can nudge Switch into providing the same functionality as COALESCE.  Also, by making sure that the last boolean expression always evaluates to True, we include a "none of the above" option that acts as a fail-safe.

Switch Function "Gotchas"

In using the Switch function in your Access projects, please be wary of the following "gotchas"

Always include an even number of expressions.

Remember, Switch always evaluates the expressions in pairs, with the first in each pair being used in a boolean test, and the second to generate the value if that boolean test is the first that evaluates to True.  If you pass an odd number of expressions, Switch will generate an error.

All arguments in Switch always get evaluated, so an error in one argument causes the whole thing to fail.

Both Switch and IIf are subject to this.  Be especially wary if you are using division in any of your expressions, and make sure you guard against any possibility that you could have a division by zero error.

If none of the boolean expressions evaluate to True, Switch will return a null.

If you want to prevent this from happening, by having some alternate value for "none of the above", then either make sure the last boolean expression is always True (use 1 = 1, or simply True, to do this), or embed your Switch expression inside an Nz expression.

Make sure you have a solid database design

Switch can be a very, very handy function, most especially for "one-off" analyses or tasks.  However, if you find you have to rely on it often in a particular project, that may be an indication that your database design is suboptimal:

For example, instead of using nested IIfs or Switch to make assignments or categorize items, often the better approach is to use another table as a "lookup".

As I mentioned in another recent question about using Switch,

If you find yourself resorting to fancy Switch/IIf jiu-jitsu, take another look at [your design]

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=