Unanswered: Problem using nz() when sorting null / missing dates
I have a query that's primary action is sorting records in the underlying table by two date fields (StartDate and FinishDate). These dates refer to work history, so some of the records don't have a FinishDate (if they are a current position).
When I used:
ORDER BY [FinishDate] DESC , [StartDate] DESC;
... the records sort correctly except that the current positions always appear on the bottom as they have NULL values in the FinishDate.
To fix this I used:
ORDER BY nz([FinishDate],#12/31/9999#) DESC , [StartDate] DESC;
... as the nz() can allow me to replace the NULLs with a very high value for sorting purposes. But for some reason only about half of the NULLs now order correctly. It seems to have something to do with the date being auto-adjusted to remove "0"s. (i.e. the "12/31/9999" date will display below 03/01/2003 I think because the "0" is removed from the day resulting in a 1st digit of "3", then compared with the 1st digit above "1" - the result being that 03 is considered greater than 12).
Does anyone know more about this quirk with nz()? Can anyone lend other suggestions as how I can work-arround?