Software

Ask something
Discussion Locked

ACCESS'97 QUERY

0 helpful
5 replies

by mozster

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In Access'97 I want to sort a date field inside a form so any empty (null) values appear first and then in order of the most recent dates after that.

At the moment its sorted in DESC order which gives me the most recent dates first but all the NULL fields go to the end.

Is there any way of doing this in Access?

5 replies

by karpi

Hello,

there are two ways you can do that:

1. enter the date-field, right-click and choose sort-order ascending.
2. change the underlying recordsource for the form : "Select * from table1 order by date_field asc"

HTH

Karpi

by mozster

If you sort Ascending then it does indeed put the NULL values first but then after that the dates appear with the oldest first and the most recent last.
Ideal order I need is:NULL VALUES, MOST RECENT DATES, OLDER DATES.

by karpi

Hello,

sorry, I thought you only wanted to have the NULL-Values first.

Here is another:

Select * from mytable1 order by iif(isnull(Datefield),cdate("12-31-2100"),datefield)

be carefull, this is a non optimizable query
and runs for a long time.

HTH
Karpi

This discussion is closed to new replies.