Switch Function

Description

Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Syntax

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n, value-n]])

The Switch function syntax has these parts:

Part	Description

expr	Required. Variant expression you want to evaluate.
value	Required. Value or expression to be returned if the corresponding expression is True.

Remarks

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. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

Switch returns a Null value if:

None of the expressions is True.
The first True expression has a corresponding value that is Null.

Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.

See Also

Choose function, IIf function, Select Case statement.

Specifics (Microsoft Access)

You can also use the Switch function in a calculated control on a Microsoft Access form or report. For example, you can use the Switch function to set the value of a control based on the value of another field. Set the ControlSource property of the control to an expression containing the Switch function.

In the following example, the Switch function returns a string that is the name of a shipper based on the value of the Freight field. If the value of the Freight field is less than $25, the Switch function returns "Speedy"; if it is greater than or equal to $25 but less than or equal to $50, the Switch function returns "United"; if it is greater than $50, the Switch function returns "Federal".

=Switch([Freight] < 25, "Speedy", ([Freight] >= 25 and [Freight] <= 50), _
    "United", [Freight] > 50, "Federal")

Note In Visual Basic code, you may want to use the more full-featured Select Case statement to return a value from a set of several choices.

Example

This example uses the Switch function to return the name of a language that matches the name of a city.

Function MatchUp (CityName As String)
    Matchup = Switch(CityName = "London", "English", CityName _
        = "Rome", "Italian", CityName = "Paris", "French")
End Function

Example (Microsoft Access)

The following example uses the Switch function to determine the appropriate language for a specified city based on the values of the ShipCountry and ShipCity fields in an Orders table. You can enter the following expression in a calculated control on a form or report. The expression is shown on multiple lines for clarity; you can also enter it on a single line.

=Switch([ShipCity] = "Madrid", "Spanish", _
    [ShipCity] = "Berlin", "German", _
    [ShipCity] = "Torino", "Italian", _
    [ShipCountry] = "France", "French", _
    True, "English")

If the city is Madrid, the Switch function returns "Spanish"; if it is Berlin, it returns "German"; and so on. If the city is not one of those listed, but the country is France, it returns "French". If the city in question is not in the list, the Switch function returns "English".