SQL Order By Clause
Immediate If (IIF) Order By Clause in Access Queries
Create a dynamic query 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 query 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 Access Order By is the SQL order by
ordinal numbers. 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 SQL Order By Examples:
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 |
You'll also need to see the SQL code for the
query to see how the SQL Order By clause works for this Access choose
command example:. SELECT Choose(Val([forms]! ...
www.blueclaw-db.com/accessquerysql/choose_function.htm |
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 |
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 |