General discussion

Locked

ACCESS'97 QUERY

By Mozster ·
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?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

ACCESS'97 QUERY

by Karpi In reply to ACCESS'97 QUERY

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
<fluctuat nec mergitur>

Collapse -

ACCESS'97 QUERY

by Mozster In reply to ACCESS'97 QUERY

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.

Collapse -

ACCESS'97 QUERY

by Karpi In reply to ACCESS'97 QUERY

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
<fluctuat nec mergitur>

Collapse -

ACCESS'97 QUERY

by Mozster In reply to ACCESS'97 QUERY

Poster rated this answer

Collapse -

ACCESS'97 QUERY

by Mozster In reply to ACCESS'97 QUERY

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums