Choose Function

Description

Selects and returns a value from a list of arguments.

Syntax

Choose(index, choice-1[, choice-2, ... [,choice-n]])

The Choose function syntax has these parts:

Part	Description
index	Numeric expression or field that results in a value between 1 and the number of available choices.
choice	Variant expression containing one of the possible choices.

Remarks

Choose returns a value from the list of choices based on the value of index. If index is 1, Choose returns the first choice in the list; if index is 2, it returns the second choice, and so on.

You can use Choose to look up a value in a list of possibilities. For example, if index evaluates to 3 and choice-1 = “one”, choice-2 = “two”, and choice-3 = “three”, Choose returns “three”. This capability is particularly useful if index represents the value in an option group.

Choose evaluates every choice in the list, even though it returns only one. For this reason, you should watch for undesirable side effects. For example, if you use the MsgBox function as part of an expression in all the choices, a message box will be displayed for each choice as it is evaluated, even though Choose returns the value of only one of them.

The Choose function returns a #Null# if index is less than 1 or greater than the number of choices listed.

If index is not a whole number, it is rounded to the nearest whole number before being evaluated.

Specifics (Microsoft Access)

The Choose function is most useful in a calculated control on a form or report. For example, you can use the Choose 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 Choose function.

= Choose([ShipVia], "Speedy", "United", "Federal")

In the preceding example, if ShipVia contains 1, the Choose function returns the first string, “Speedy”. If it contains 2, the function returns the second string, “United”, and so on. If ShipVia contains 0, the Choose function returns a Null.

The next example shows how you can ensure that a string is returned even if the field contains 0.

= Choose([ShipVia] + 1, "none", "Speedy", "United", "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 Choose function to display a name in response to an index passed into the procedure in the Ind parameter.

Function  GetChoice(Ind As Integer)
    GetChoice =  Choose(Ind, "Speedy", "United", "Federal")Function

You can use the Choose function to set the value of one field based on the value of another field. For example, suppose you have an Orders table that contains a field called OrderDate. You could create a calculated control on a form to display the day of the week on which an order was placed. The following example uses the Weekday function to return the numeric representation of the day of the week corresponding to the order date. The Choose function returns a string containing the name of the day of the week based on that numeric value.