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.
The Switch function evaluates each pair of expressions, and returns the value associated with the first expression that evaluates to True.
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:
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:
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:
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...ElseIfIf 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 CaseSelect 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
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
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.
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:
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:
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
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):
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 ReasonFROM 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 ReasonFROM ((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;