Inside This ArticleUse MS Access IIF function in ORDER BY clause
In this tutorial, IIF function is explored further to show how to use it
in ORDER BY clause for conditionally ordering.
There are two examples used here. They demonstrate how to use IIF to dynamically sort query results.
Code example 1:
In Employees table, we sort EmplyeeID, FirstName, and LastName by user's input.
Then, we want to know how many products are in High category and how many are in Low category.
| Query Name |
Qry_Func_IIF_5 |
| Logic |
If user enters EmployeeID in the parameter value input box, the result is ordered by EmployeeID.
If user enters FirstName, the result is sorted by FirstName. All other inputs (including blank), the result
will be ordered by LastName.
|
| SQL View: |
SELECT EmployeeID,
FirstName,
LastName
FROM Employees
ORDER BY IIf([Order By:]='EmployeeID',[EmployeeID],
IIf([Order By:]='FirstName',[FirstName],[LastName]));
|
| Datasheet View |
9 records returned which are ordered by FirstName 
|
Code example 2:
In this example, we order employees' first name and last name by a derived value.
This value is a modulus of current time in second divided by 2.
| Query Name |
Qry_Func_IIF_6 |
| Logic |
If the modulus of current second divided by 2 equals 0, the query result is ordered by LastName.
Otherwise, it is ordered by FirstName.
|
| SQL View |
SELECT FirstName,
LastName,
Second(Now()) AS [Second],
Second(Now()) Mod 2 AS Mod
FROM Employees
ORDER BY IIf((Second(Now()) Mod 2)=0,[LastName],[FirstName]);
|
| Datasheet View |
9 records returned for odd seconds: 
|
To sum up, in this tutorial we have illustrated how to use IIF function in ORDER BY clause to dynamically order
query results. For more complex use of IIF function, refer to Part 4 of IIF function.
Copyright© GeeksEngine.com
|
Inside This Article
Related Articles:
Other Recent Articles from the MS Access category:
|
