Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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
0
markp99
Asked:
markp99
  • 3
  • 2
1 Solution
 
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
 
Jim Dettman (Microsoft MVP/ EE MVE)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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now