Access: Switch Function

In Access, the Switch function evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

The syntax for the Switch function is:

Switch ( expression1, value1, expression2, value2, … expression_n, value_n )

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren’t properly paired, a run-time error occurs. Switch evaluates all of the expressions, even though it returns only one of them. Switch returns Null in two cases: if none of the expressions is true, or if the first True expression has a corresponding value that is Null.

expression1, expression2, expression_n is a list of expressions you want to evaluate.

value1, value2, … value_n is a list of values to be returned if the corresponding expression is True.

Example

Switch (OwnerID=1, "Table", OwnerID=2, "Desk", OwnerID=15, "Mirrow")     

returns: 
     "Table", if the OwnerID field is 1.
     "Desk", if the OwnerID field is 2.
     "Mirrow", if the OwnerID field is 15. 

VBA Code

Dim Result as String 
Result = Switch (OwnerID=1, "Table", OwnerID=2, "Desk", OwnerID=15, "Mirrow") 

This example uses the Switch function to return the name of an item that matches the OwnerID.

SQL query

You can also use the Switch function in a query.

SELECT Switch(OwnerID=1,"Table",OwnerID=2,"Desk",OwnerID=15,"Mirrow") AS Expr1
FROM Orders 

Note

You cannot test this function online. It works in Access only.