Switch Function

Description

Evaluates a list of expressions and returns a 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	Variant expression you want to evaluate.
value	Value or expression that is returned if the corresponding expression is True.

Remarks

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated as they appear in the list from left to right and the value associated with the first expression to evaluate 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.

Specifics (Microsoft Access)

The Switch function is most useful in a calculated control on a 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 a Visual Basic module, 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")Function

The following example uses the Switch function to determine the appropriate language for a specified city by evaluating the ShipCountry and ShipCity fields against a table named Language that contains country names and languages. 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")