Microsoft Access ProductsMicrosoft Access Query Sorting on Multiple Date or Numeric Fields with Blank (Null) Values

For tables that our users edit, we often have a set of four fields for a simple "Audit Tail" that we programmatically fill with who and when it was created and last edited. (This is different from an Audit Trail where we keep a copy of the whole record in another table). The Audit Tail doesn't save all the changes that were made over time, but we have the original author and last person who modified it, and when they did so. In many cases, that's sufficient.

We use fields such as [CreatedBy], [CreateDate], [EditedBy], [EditDate].

For clarity, simplicity, and to store less data, the EditedBy and EditDate fields are blank when the record is created. It's only updated when the record is modified after it was originally saved.

We often want to display in a form the most recently modified records. If the EditDate field were filled for every record, we could easily create a MS Access query with an ORDER BY clause like this:

ORDER BY EditDate DESC, CreateDate DESC

Here's the example in the MSAccess Query Designer:

Microsoft Access query sort design

The result is this (apologies to everyone who doesn't use American date formatting; the date formatting doesn't matter):

Microsoft Access query sort datasheet

If the EditDate is blank, this sorts all the modified records above the unmodified records (blank EditDate field). What we'd like is a mixture of the most recently modified and created records.

Of course, this doesn't only apply to our Audit Tail example, but any combination of date or numeric fields that you'd like to sort and may contain null values.

This can be easily done by sorting on a calculated field based on the combination of the Create and Edit fields. We sort on the EditDate field, and if it is blank, use the CreateDate field:

ORDER BY IIf([EditDate] Is Null, [CreateDate], [EditDate]) DESC

Microsoft Access query sort calculated expression

By using the Immediate If IIF() function and Is Null, we get the CreateDate value if EditDate is blank and the EditDate value if it exists. We can see how it's sorted by the Expr1 column here:

Microsoft Access query sort calculated expression results

Note that we reordered the CreateDate and EditDate columns from our original example.

To clean it up, unselect the calculated field which allows us to still sort on it but not show it.

Hide Calculated Field in Microsoft Access Query

The result is what we originally wanted: the list of records sorted by most recently created or modified:

Microsoft Access Query with Hidden Sort Fields

Above, we used the Is Null comparison within the IIF function. Instead, we could have used the VBA IsNull() function:

ORDER BY IIf(IsNull([EditDate]), [CreateDate], [EditDate])

Microsoft SQL Server DevelopersThis works perfectly fine in MS Access, but will not work with SQL Server. For best practices, avoid using IsNull() in queries so that upsizing to SQL Server is less of an issue. By having an Access VBA function in your query, if the tables are linked to SQL Server, the processing will have to occur in Access. Enabling SQL Server to run queries is much more efficient.

Compatible with SQL Server

ORDER BY IIf([EditDate] Is Null, [CreateDate], [EditDate]) DESC

Some may think using the Access VBA function Nz (NullToZero) is equivalent to our suggested combination of IIf and IsNull. However, Nz() is not the same and does not work for this situation. You could try this expression thinking it would produce the same result:

Nz([EditDate], [CreateDate])

However, the NZ function returns a string. When it comes to sorting dates and numbers, we need to sort on the actual value rather than alphabetically. For instance, when sorting dates in ascending order, we want September (9) before October (10). String sorting (alphabetical) would place 10 before 9 since it sorts on the 1. Similarly for days if the months match, and of course, it wouldn't sort by year first.

Notice the problem in the example below where we are sorting by the Expr1 calculated field based on NZ in DESCENDING order. March 19 should be at the top, but sorts below March 4th because alphabetically 4 is greater than the 1 in 19.

Incorrect Sort Order when Using NZ function

Have any suggestions or comments? Head to our blog post Microsoft Access Query Sorting on Multiple Date or Numeric Fields with Blank Values and leave us your feedback.

Total Access Emailer 2022
Email Everyone in Your Access List!

Email personalized messages with attached reports from Microsoft Access

Total Visual Agent 2021
Automate Access Database Chores!

Automate Microsoft Access database compact and other chores

Microsoft Access to SQL Server Upsizing

Updated Microsoft Access to SQL Server Upsizing Center with whitepapers, resources, and SQL Server Express Downloads

Free Product Catalog from FMS