Order By Clause

Immediate If (IIF) Order By Clause in Access Queries

Create a dynamic order by clause using the iif (immediate if) statement to change the 'Order By' statement in a query based on user input.  There are many instances where you would like to sort a form or report on different fields depending on a user's selection.

The following is an SQL example of an Access dynamic Order By clause.   This example assumes you have a form ('F_Emp') with an Option Group ('Sort_Option') with two possible choices.  Option 1 is for sorting by employee name and option 2 is for sorting by employee number.   Variable sorting can be accomplished in the Report however it is much more efficient to put this in the SQL code.

Select M_Employees.Name, M_Employees.Emp_Number, M_Employees.Address
From M_Employees
Order by
IIf(Forms!F_Emp!Sort_Option=1, M_Employees.Emp_Name, M_Employees.Emp_Number);

An alternative form of the Order By statement is to use ordinals.  Ordinals are numbers that refer to the fields in the select clause.  Example: ordinal 1 refers to M_Employees.Name, and ordinal 2 refers to M_Employees.Emp_Number. IIf(Forms!F_Emp!Sort_Option=1, 1, 2); This form of the IIf clause is very useful when you need to nest IIf clauses because of more than two sort choices.

The immediate if (IIF) statement can also be used to create dynamic where query statements.

More Order By Examples:

Select Top in Access

Select Top 1 Query1.Submit_Date from Query1 Order by Query1.Submit_Date DESC;. Now you have the 3rd submit date. Note that sorting the Query1 records in ... www.blueclaw-db.com/accessquerysql/microsoft_access_2002_query.htm

Access Choose Function Example

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]! ... www.blueclaw-db.com/accessquerysql/choose_function.htm

Choose Function in Microsoft Access

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, ... www.blueclaw-db.com/accessquerysql/choose.htm

Concatenate Records Text Field

Reservation_ID & " order by rsort" Set rsroute = db. ... sqltext = "Select Airline_ID,Flight_No,Connect_Time from M_Passengers where Reservation_ID=" & rst! ... www.blueclaw-db.com/concatenate_multiple_records_one_field.htm