MSAccess: Date Sort, Nulls to Bottom?

Hello,

I've place a collection of radio buttons on a form to permit various sorting options.  One of them is to sort on a date field.  I have attempted to sort ascendingm with NULLS at teh bottom using the following approach:

Forms!MainForm![QAReport subform].Form.OrderBy = "nz([Due to Client],'ZZZ') ASC"

This does move the NULLS to the bottom, but the dates are not sorting correctly.  Here is a sample - close but not quite:

Due From QA
5/26/2009
5/27/2009
5/28/2009
5/29/2009
5/30/2009
5/6/2009
5/7/2009
5/8/2009
5/8/2009
5/9/2009
5/9/2009
5/9/2009
6/1/2009
6/11/2009
6/16/2009
6/16/2009

Any suggestions?

Mark
markp99Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Change to this:
Forms!MainForm![QAReport subform].Form.OrderBy = "CDat(nz([Due to Client],'12/31/2999')) ASC"
and create another column in your underlying data source defined as:
nz([Due to Client],' ')
 and display that in the form.  You should get the right sorting then.
JimD.  
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Any suggestions?>>
is [Due to client] a text field or a date/time field?
JimD.
0
 
markp99Author Commented:
This is a date/time field from an external (linked) SQL table. If I right click on the column header and sort, dates are sorted correctly with NULLS at the top.
0
 
markp99Author Commented:
Simply using:

Forms!MainForm![QAReport subform].Form.OrderBy = "CDate(nz([Due to Client],'12/31/2999')) ASC"

Solved my issue.

Thanks.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Yes, the first change would take care of the sort, but I didn't think you'd want to see 12/31/2999 in place of the NULLs in you date column which is why I suggested defining a new column for display.
JimD.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.