Date field in Access refusing to sort

Hi all,

I am trying to sort dates in access. I know, the question is posted everywhere (I know because I've looked for the answer), except that not a single answer has proven right.

In a continuous form, I am trying to sort a date field (Date/Time (Short Date)). The following code is On Click event.

Please Login or Register  to view this content.

This works to keep the dates at the top and the blanks to the bottom, however, the dates are displayed like so

Please Login or Register  to view this content.

This is completely bizzare and I don't understand why it's not working.

It is supposed to look like:

Please Login or Register  to view this content.

Can anybody please advise why this is not working and how to make it do what I need it to do.

Cheers

I think I have an answer to my own question.

Some people had provided this similar idea in various responses around the web. So many answers just didn't work, but when I came to understand what the issue was, I did this:

Please Login or Register  to view this content.

That old NULL sure does throw things around a bit. Instead of Null (e.g. Nz(...etc...)) I used the field length (0), and set the falsepart to 99999, which puts the date about 50 years into the future.

This then sorts the blanks (i.e. 99999 dates) to the bottom, and the dates in the required order at the top. the future dates are of course, hidden.

If you use this in a query instead, simple add the IFF function above to a new calculated field, and sort on it (not the named field). You should also not display it.

Hope this helps someone in future.

Cheers