Home   Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles   Search   Contact  Privacy  Links
SQL Order By Statement in Access
Variable SQL Order By
Microsoft Access IIF Command
Home > Tutorials > Query Tutorials > Access Dynamic Query
 






Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
SQL 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
Access SQL Select Top
SQL Union Query
SQL Update Query
Custom Query Function

 

MultiSelect Parameter Forms Access Tutorial


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.



Are you just starting out on your quest for a small business database? Need some advice or someone to bounce ideas off of? We are happy to chat with you to discuss options to your situation.

Did you know that most template databases often do not do exactly what you need done? We can create a custom template that fits your needs exactly.


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.

Finally, a third alternative is to use the Access Choose function to create the dynamic query:

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

See more choose function examples in our tutorial on the choose function.

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







A Blue Claw Software Design Template:

Subscription Order Control Database
 









A Blue Claw Database Design Article:

Why Choose Microsoft Access
 









Blue Claw Database Design Downloadable Tutorial:
Download Continuous Form Dependent Combo Box  








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


Contact Information
Copyright 2000-2017 Blue Claw Database Design
Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials