Home   Fees/Services   Access Templates   Tutorials  Tutorial Downloads   Articles   Search   Contact  Privacy  Links
Choose Function Tutorial
SQL Choose Command Example
 






Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Access Append Query
Master Detail Update
Order By Query
SQL Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Select Top
Union Query
SQL Update Query
Custom Query Function

 

MultiSelect Parameter Forms Query Tutorial


Choose Function Example

The Choose command is a power feature you need to master. Give us a call and we will help you.
410-708-1417

Choose function in MS Access: Have you ever had to create several reports that were almost identical but had different order for the columns and a different sort order? Many of these reports can be done in one  intelligent report and query combination with the Access Command: Choose function.
Access Choose function query example setup:  We have a form called Form1.  On Form1 are two combo boxes (Field1_Combo, Field2_Combo), and a 'Preview Report' button:



Since 2000 we have developed nearly 500 databases for clients throughout the world. See a sample of them at our recent clients page.

Do you and your clients need access to data on the Internet? We can create a secure system to match your requirements perfectly.


Access Choose Function

Both combo boxes have the same Row Source.  Note that there are two columns in the row source; the first is a number (1, 2) and the second is the name of the field to be included in the report and sorted by.

The choose code looks like this:

Access choose command example    Access choose function

Note that we will always retrieve the employee's Pay_Rate field, however this could be variable as well.  You'll also need to see the example code for the query to see how the Order By clause works for this Access choose command example:

SELECT Choose(Val([forms]![form1]![field1_combo]),[SSN],[Employee_No]) AS Field1,
Choose(Val([forms]![form1]![field2_combo]),[SSN],[Employee_No]) AS Field2,
M_Emp_Pay.Pay_Rate
FROM M_Emp_Pay
ORDER BY
Choose(Val([forms]![form1]![field1_combo]),[SSN],[Employee_No]),
Choose(Val([forms]![form1]![field2_combo]),[SSN],[Employee_No]);

Here's the resulting report using the choose function:

New! Download Choose Function Command Demonstration Database.

Choose function example

The second trick is how I got the column headings to come out correctly... All that's required is one line of VBA code for each label.. but before I show that here is the design view of the report:>

Choose Function in Access

Ok, let's see how the VBA code assigns the correct values to the labels:

Option Compare Database

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)

Me.Field1_Label.Caption = Replace(Forms![form1]![Field1_Combo].Column(1), "_", " ")
Me.Field2_Label.Caption = Replace(Forms![form1]![Field2_Combo].Column(1), "_", " ")

End Sub

Don't be confused by the Replace function command... all that does is get rid of underscore character in the field name (Employee_No to Employee No).

Using the Access Choose function query can be extended to numerous fields and could probably be used for aggregate functions too, but I haven't tried that yet.  The alternative to using the Access Choose command is to write many lines of VBA code in the report or behind the parameter form (Form1).

You can also use the choose function to pass parameters to an Access Query.

More Choose Command Examples

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.







A Blue Claw Software Design Template:

Airline Reservations Database Template
 









A Blue Claw Database Design Article:

Learn How To Select A Consultant
 









Blue Claw Database Design Downloadable Tutorial:
Inventory Calculations Download ( Single User)  








Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365
 


Contact Information

Copyright 2000-2017 Blue Claw Database Design