Browse All Articles > Using the Switch Function in Microsoft Access
By Patrick G. Matthews
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:
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:
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:
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):
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:
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:
Instead of doing it that way, I recommended using If...ElseIf or Select Case, as follows:
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.
In her question Sorting in Reports, EE Member kristenbednarz wrote:
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:
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:
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.
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:
The Asker provided this screenshot of one line of the query results:
![Example 2]()
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:
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.
In the last example, ProdOps needed a query that would resolve managerial assignments with multiple overrides:
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:
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:
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.
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 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!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
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
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, ProspectNameI 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 SomeTableIn 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 SomeTableAlternatives 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")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 SelectUsing 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);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 | 4creates 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:
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 tblProjectNote 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
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;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;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!
=-=-=-=-=-=-=-=-=-=-=-=-=-
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (8)
Commented:
bol
Commented:
Commented:
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
Commented:
Author
Commented:View More