Sorting records by date

[Paul Fenton]

I have a form based on a query and the records are sorted by "Start
Date". Not all the records for a particular client will have a date
in that field.

Access treats those records as being older than the oldest date that's
there and so they appear at the top of the form, the first few
records. What I want to do is sort the records oldest to newest, but
put the blank records at the end of the listing.

The OrderBy property lets me do that, but sorts newest to oldest, not
what I want.


Paul Fenton
(e-mail address removed)

 

[Joan Wild]

Put the following in a column in the grid
Nz([DateField],#1/1/4000#)
and sort ascending on that

 

[Tom Wickerath]

Another method that you can use is to insert a new calculated field into your query.

Field: Expr1: IsNull([DateField])
Show: Deselected

This will evaluate to True (-1) or False (0) for each record. Sort this field Ascending
to put records with a null DateField at the top, or Descending to put them on the bottom.

Tom
___________________________________


Put the following in a column in the grid
Nz([DateField],#1/1/4000#)
and sort ascending on that

--
Joan Wild
Microsoft Access MVP
___________________________________


I have a form based on a query and the records are sorted by "Start
Date". Not all the records for a particular client will have a date
in that field.

Access treats those records as being older than the oldest date that's
there and so they appear at the top of the form, the first few
records. What I want to do is sort the records oldest to newest, but
put the blank records at the end of the listing.

The OrderBy property lets me do that, but sorts newest to oldest, not
what I want.


Paul Fenton
(e-mail address removed)