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

Background: Sorting on Audit Tails

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:

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

Problem with Null Values

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.

Solution: Sort on a Calculated Field

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

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:

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.

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

Avoid Using IsNull Function in Microsoft Access Queries

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])

This 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

Do Not Use the Null To Zero Nz() Function

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.