Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Location
    China
    Posts
    54

    Unanswered: Problem using nz() when sorting null / missing dates

    Hi everyone,

    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?

    Thanks in advance,

    Richard.

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I to have notice that NZ doesnt seem to like working with dates....

    You can do a number of things...
    1) use CDate(Nz())
    2) use an Iif(isnull(Yourfield),#12/31/9999#,YourField)
    3) create a small function to do it

    Good luck & Greetz

  3. #3
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    Namliam,

    Thanks for the great advice. I simply used the CDate(Nz()) format that you recommended.

    Problem solved. Cheers,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •