How to Use the Switch Function in Microsoft Access on Windows 11
Posted on 19th June 2023
In this article, we will show you how to use the Switch function in Microsoft Access on Windows 11. The Switch function allows you to evaluate a list of expressions and return the result for the first expression in the list that is TRUE.
Microsoft Access Switch Function Syntax
The syntax for the Switch function is as follows:
SWITCH ( expression1, value1, expression2, value2, ... , expressionN, valueN )
expression1, expression2, … expressionN
The list of expressions to evaluate.
value1, value2, … valueN
The list of values to return.
Microsoft Access Switch Function Examples
Let’s take a look at some examples of the Switch function.
Example 1
In this example, we will use the Switch function to return the day of the week based on the day number.
SELECT Switch(
Weekday(Date())=1, "Sunday",
Weekday(Date())=2, "Monday",
Weekday(Date())=3, "Tuesday",
Weekday(Date())=4, "Wednesday",
Weekday(Date())=5, "Thursday",
Weekday(Date())=6, "Friday",
Weekday(Date())=7, "Saturday"
) AS DayOfWeek;
Example 2
In this example, we will use the Switch function to return the discount rate based on the number of products purchased.
SELECT Switch(
ProductsPurchased<10, 0.0,
ProductsPurchased<20, 0.1,
ProductsPurchased<50, 0.2,
ProductsPurchased=100, 0.4
) AS DiscountRate;
Microsoft Access Switch Function Tips
Tip 1
If none of the expressions evaluate to TRUE, the Switch function will return NULL.
Tip 2
If more than one expression evaluates to TRUE, the Switch function will return the value for the first expression in the list that is TRUE.
Tip 3
If you want to return a value even if none of the expressions evaluate to TRUE, you can use the following syntax:
SWITCH ( expression1, value1, expression2, value2, ... , expressionN, valueN, TRUE, value )
Microsoft Access Switch Function Compatibility
The Switch function is available in Microsoft Access 2010 and later versions.
Microsoft Access Switch Function Examples in VBA Code
Example 1
The following code shows how to use the Switch function in a VBA code.
Dim day As Integer
day = Switch(
Weekday(Date())=1, 1,
Weekday(Date())=2, 2,
Weekday(Date())=3, 3,
Weekday(Date())=4, 4,
Weekday(Date())=5, 5,
Weekday(Date())=6, 6,
Weekday(Date())=7, 7
)
Debug.Print day
Example 2
The following code shows how to use the Switch function with the IIf function in a VBA code.
Dim discount As Double
discount = Switch(
ProductsPurchased<10, 0.0,
ProductsPurchased<20, 0.1,
ProductsPurchased<50, 0.2,
ProductsPurchased=100, 0.4,
True, 0.5
)
discount = IIf(discount=0.5, 0.0, discount)
Debug.Print discount
When you want to return more than one value from a function, you can use the Switch function. The Switch function takes two parameters: the value to test and the list of return values. The list of return values is a set of comma-delimited values. The first value is the value to return if the test value is true, the second value is the value to return if the test value is false, and so on. If the test value is not true or false, the Switch function will return the value null.
You can use the Switch function in a query in the WHERE clause, in a form or report control source, or in a macro action.
The following example shows how to use the Switch function in a query. The query returns a list of employees and their job titles. The job titles are returned based on the value of the JobTitle field. If the job title is 1, the query returns Salesperson. If the job title is 2, the query returns Manager. If the job title is 3, the query returns President. If the job title is anything else, the query returns NULL.
SELECT LastName, FirstName,
Switch(JobTitle=1,”Salesperson”,
JobTitle=2,”Manager”,
JobTitle=3,”President”,
True,NULL) AS JobTitle
FROM Employees;
You can also use the Switch function in a form or report control source. The following example shows how to use the Switch function in a form control source. The form displays a list of employees and their job titles. The job titles are returned based on the value of the JobTitle field. If the job title is 1, the form displays Salesperson. If the job title is 2, the form displays Manager. If the job title is 3, the form displays President. If the job title is anything else, the form displays NULL.
=Switch(Employees!JobTitle=1,”Salesperson”,
Employees!JobTitle=2,”Manager”,
Employees!JobTitle=3,”President”,
True,NULL)
You can also use the Switch function in a macro. The following example shows how to use the Switch function in a macro. The macro displays a message based on the value of the JobTitle field. If the job title is 1, the macro displays the message You are a salesperson. If the job title is 2, the macro displays the message You are a manager. If the job title is 3, the macro displays the message You are the president. If the job title is anything else, the macro displays the message You have no title.
Switch(Employees!JobTitle=1,”You are a salesperson.”,
Employees!JobTitle=2,”You are a manager.”,
Employees!JobTitle=3,”You are the president.”,
True,”You have no title.”)
The Switch function is a built-in function in Microsoft Access. You can use the Switch function in a query, in a form or report control source, or in a macro. The Switch function takes two parameters: the value to test and the list of return values. The list of return values is a set of comma-delimited values. The first value is the value to return if the test value is true, the second value is the value to return if the test value is false, and so on. If the test value is not true or false, the Switch function will return the value null.
