Sql Query | Access Choose Function

Access Choose Function Example

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 SQL 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:
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 query looks like this:

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 SQL 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_RateFROM M_Emp_PayORDER BY Choose(Val([forms]![form1]![field1_combo]),[SSN],[Employee_No]), Choose(Val([forms]![form1]![field2_combo]),[SSN],[Employee_No]);
Here's the resulting report:
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:>
Ok, let's see how the VBA code assigns the correct values to the labels:
Option Compare DatabasePrivate 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 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 SQL 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.