Sorting with null / blank fields last

  • Thread starter Thread starter justme
  • Start date Start date
J

justme

How can I have a query sort a field with dates in such a manner that blank /
null entries are last?

TIA for the help!

JustMe
 
You could create a calculated field such as the following and use it as your
primary or first sort:

IIf(IsNull([MyDateField]),9999,1)

Then make your Date field the second sort.

hth,
 
Assuming a field named "MyField", type this into the Field row of your
query:
IsNull([MyField])
and choose Descending in the Sorting row.
Then add MyField in a column to the *right* of that.

How it works:
The calculated field returns True if the field is null, else False.
In Access, True is -1, and False is zero.
Sorting Descending sorts the zero (False) before -1 (True.
 
Back
Top