<

Using the Switch Function in Microsoft Access

Published on
86,343 Points
62,743 Views
26 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
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:
Describe the syntax for the Switch function;
Discuss alternatives to Switch;
Present sample use cases for Switch, based on actual Experts Exchange questions; and
Cover common "Gotchas" and challenges in using the Switch function.


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:
At least one pair of expressions is required; additional pairs are optional
You must pass pairs of expressions to Switch; passing an odd number of expressions will generate an error
In each pair, the first expression must either generate a Boolean True or False value, or a value that can be implicitly converted to a Boolean value, such as a number, or a string such as "True" or "False".  If the first expression in any pair does not explicitly or implicitly evaluate to a True or False value, an error results
If the first expression in a pair evaluates to a number, it is implicitly converted to False if the number equals zero, or True if the number does not equal zero
Access will evaluate all of the expressions passed to the Switch function, so if any of those expressions generate an error, the function will generate an error
If none of the boolean expressions evaluate to True, Switch returns Null.  In addition, if the value expression corresponding to the first boolean expression evaluating to True is itself Null, then Switch returns Null
To force a non-null return value for a "none of the above" scenario, either always include a final boolean expression that always evaluates to True, or nest your Switch formula inside Nz

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:
OK: due date is either today, or within the next 4 days
Overdue: due date is passed, but not by more than 5 days
Very Overdue: due date passed by more than 5 days
Future: due date is 5 or more days in the future

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

Open in new window


NOTE: While the above example is appropriate for a "one-off", ad hoc analysis, if such a query were needed on a continuing basis, best practice would dictate using a table-driven approach to assign appropriate due date status.


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

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

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

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")

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

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);

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

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:

Example 2
In this case, as long as we could assume that:
The dates always increase as we move to the "next" drop number; and
There is never an instance where Drop[N]Billed is true but Drop[N-1]Billed is false

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

Open in new window


Note the following about this solution:
Since the DropNBilled columns were yes/no, or boolean columns, simply referencing each of those columns rendered a boolean expression in and of itself for Switch to evaluate
Since the dates always increased as we moved from DateN to Date[N+1], and we wanted to grab the latest date, we had to work "backwards" in the Switch expression, testing the last Drop first

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

Example 3 Table Layout
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;

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;

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:
Your data may be denormalized;
You may need additional "lookup" tables to perform assignments;
Et cetera

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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
26
Comment
8 Comments
 
LVL 16

Expert Comment

by:Jerry Paladino
Patrick,

Great Article!  Since you introduced me to the Switch function several months ago I have converted the majority of nested IIF statements in my Access queries to Switch.  They are much easier to write and easier to understand when you come back to them later.   This one got my YES vote!

Thanks Again!
Jerry  
0
 
LVL 93

Author Comment

by:Patrick Matthews
Glad to help, Jerry, and thanks for your vote!
0
 
LVL 12

Expert Comment

by:telyni19
Well-written, thorough analysis of a lesser known function. Like the author, I started with Excel, and then moved to VBA, which has the more controllable Case statement, so I have never used Switch. I will have to take a look the next time I find myself wrestling with nested IIfs in Access (which fortunately doesn't happen too often since I do use lookup tables and the like).
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 54

Expert Comment

by:b0lsc0tt
Thanks!  It was very interesting.  Even though I am not sure I will have a chance to use it in Access (I will keep my fingers crossed) I found it very useful in general.  That "If" alternative is something very useful.

bol
0
 
 

Administrative Comment

by:Kevin Cross
Patrick:

Following an discussion amongst the page editors, your article has been nominated and approved to receive the highest award available to us for Articles: Editors Choice.

Congratulations!

Thank you very much for your contribution to the community and we look forward to seeing more of your work.

Best regards,

mwvisa1
EE Page Editor
0
 

Expert Comment

by:ReneeM787
This is so almost what I am looking for.  All of the examples result in string data after testing.  Is it possible to return the value in a field in an unrelated table?  For example, I have records I need to tie to data in a crosstab query.  In the query there is a conversion table that converts date to "OctRate".  I'd like to use Switch("OctRate", [OctRate]).  But that doesn't want to work.  Any suggestions?  Thanks!
0
 

Expert Comment

by:S Avila
Hello and thank you for this guide.
I need the output values of this sorting system to be numbers, since they are tax ranges, I normally change query data types in the Property Sheet, but it does not let me in the Switch data. Is there a way to change it or an alternative function to overcome this? Thank you
1
 

Expert Comment

by:Feral Cypher
Why do you say Access doesn't have a Case  Statement?  I use it all the time.   Please clarify.
0

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month