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:
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.
Advertisements