Very Computer

Hi all. This is question is obstensibly about Access, but deals with SQL
syntax, which is why I am cross-posting.

I've got a table called 'MyTable' with a date field called 'MyDate'. I want
to select the values from that field and display them in ascending order.
E.g.,

"SELECT MyDate FROM MyTable ORDER BY MyDate ASC"

However, it always puts the empty (Null) fields up top when it returns the
results. Is there any way I can convert the MyDate type in SQL so that the
Null fields appear at the bottom of the list?

Any help would be appreciated. Thanks!

--Philip

Do:

ORDER BY COALESCE(MyDate, '99991231')

--
- Anith
( Please reply to newsgroups only )

This would be a great solution in SQL Server, but would bomb in Access :-)

Philip should use Nz instead of COALESCE in his Access query.

Bob Barrows

I should've read the question first :-(

--
- Anith
( Please reply to newsgroups only )

Thanks Bob and Anith. Both Coalesce() and NZ() were new to me, and were
interesting to learn about. Unfortunately, I should have been more specific.
I'm using ADO via an ASP page to get to an MS Access DB. So I couldn't get
either function working.

But, by searching around for equivalents to those two methods, I finally
found a solution that works beautifully:

ORDER BY IIF(ISNULL(MyDate), #1/1/2050#, MyDate) ASC

The date value in 2050 works fine - at least until 2050, ha ha. But that's
for a future programmer to worry about.

Thanks again for your quick responses & help!

--Philip

Quote:> The date value in 2050 works fine - at least until 2050, ha ha. But that's
> for a future programmer to worry about.

That's why people suggested using #12/31/9999#.

That's the largest date Access can hold, so you shouldn't ever run into
problems with it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele