Home  Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles  Search  Contact  Privacy  Links
Selectable SQL Order By in Microsoft Access Queries
Dynamic Order By Clause
SQL Order By Statement in Access  
Home > Tutorials > Query Tutorials > Access Dynamic Query
 

 

 


SQL Order By Clause

Create a complex 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 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:

Access Choose Function Example
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.

Choose Function Access Dynamic Query

 

 

 

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
 





Contact Information

Copyright 2000-2014 Blue Claw Database Design
Waterfront Home For Sale