Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Query Examples
SQL Choose Function Example

Choose Command Tutorial SQL Queries

Access Database Tutorials>SQL Queries>Choose Command

Download Access Program


Aggregate Functions
Child/Parent Table Query
Choose Function
SQL Choose Function +
Crosstab/Pivot Query
SQL Crosstab Query +
DateTime Query
SQL Delete Query
External Link to Tables
Filter Report Records
Group By Clause
Having Clause Query
Histogram Query
Insert Into Query/Append
Master/Detail Updates
SQL Order By Dynamic
Parameter Query
SQL Predicate Example
Self Join Query
Access Scalar Query
Select Statement
Select Top 1 Query
Union Query Example
Update Query Example


Choose Command in Microsoft Access

The choose command is similar to the Decode function in Oracle.  Assume the same setup as the Dynamic Order By Clause example but add a 3rd option for sorting by Address.

See below how the choose function expands your capabilities.

Select M_Employees.Name, M_Employees.Emp_Number,
M_Employees.Address
From M_Employees
Order by
Choose(Val(Forms!F_Emp!Sort_Option),M_Employees.Emp_Name,
M_Employees.Emp_Number, M_Employees.Address);

Note:  Entry in Forms!F_Emp!Sort_Option must be an integer and it is best to force it to a number with the Val() function.  You can have up to 29 options and you can use the Choose Function in each part of the query except the From clause.

New! Download Access SQL Choose Command Demonstration Database.

The choose function can be used in each part of a query, except possible the From clause.

2nd Choose Example

My boss wants an employee salary report and on odd days of the month he wants the data to show by last name then first name.  On the even days of the month he wants the data the other way, first name followed by last name.

Just in case you ever have a boss like that - here is the solution:

Create a parameter form for the report.  Here we have created Form1.  Then create the SQL query text using the choose function as shown:

select Choose statement

Below you'll see that when we change the choice field to 2 the last name appears first followed by the first name:

Choose Command, Decode Command

The slight trick to this solution is that we use two Choose function statements in each query.  The first choose is used for the Name1 field and the second choose is used for the Name2 field.

Additional Choose Command Information:
 

DoCmd OpenReport in Microsoft Access

crit = "Flight_ID" & Choose(get_global("flight_type"), ">-1", "=0", "<>0") output: On Error GoTo no_data Select Case Me.Select_Report Case 1 DoCmd. ...
www.blueclaw-db.com/docmd_openreport.htm


 

We now offer web-based SQL/Server application development in partnership with Integrated Databases, Inc. (new projects only).



Contact Information

SQL Queries
MS Access SQL Queries 2007 2003 2000  All Windows Versions