totn Access Functions

MS Access: Choose Function

This MSAccess tutorial explains how to use the Access Choose function with syntax and examples.

Description

The Microsoft Access Choose function returns a value from a list of values based on a given position.

Syntax

The syntax for the Choose function in MS Access is:

Choose ( position, value1, value2, ... value_n )

Parameters or Arguments

position
Position number in the list of values to return.
value1, value2, ... value_n
List of values.

Returns

The Choose function returns any datatype such as a string, numeric, date, etc.
If position is less than 1, the Choose function will return a null value.
If position is greater than the number of values, the Choose function will return a null value.

Note

  • If position is a fraction (not an integer value), it will be rounded to the nearest whole number.

Applies To

The Choose function can be used in the following versions of Microsoft Access:

  • Access 2019, Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000

Example

Let's look at how to use the Choose function in MS Access:

Choose(1, "Tech", "on", "the", "Net")
Result: "Tech"

Choose(2, "Tech", "on", "the", "Net")
Result: "on"

Choose(3, "Tech", "on", "the", "Net")
Result: "the"

Choose(4, "Tech", "on", "the", "Net")
Result: "Net"

Choose(5, "Tech", "on", "the", "Net")
Result: NULL

Choose(3.75, "Tech", "on", "the", "Net")
Result: "the"

Example in VBA Code

The Choose function can be used in VBA code in Microsoft Access.

For example:

Dim LValue As String

LValue = Choose(1, "Tech", "on", "the", "Net")

In this example, the variable called LValue would contain "Tech" as a value.

Example in SQL/Queries

You can also use the Choose function in a query in Microsoft Access.

For example:

Microsoft Access

In this query, we have used the Choose function as follows:

Expr1: Choose([SupplierID],"IBM","HP","NVidia")

This query will return the following:

  • If the SupplierID field is 1, then the Choose function will return "IBM".
  • If the SupplierID field is 2, then the Choose function will return "HP".
  • If the SupplierID field is 3, then the Choose function will return "Nvidia".

The result will be displayed in a column called Expr1. You can replace Expr1 with a column name that is more meaningful.

For example:

SupplierName: Choose([SupplierID],"IBM","HP","NVidia")

The results would now be displayed in a column called SupplierName.