Re: Simple SQL ORDER BY doesn't work!

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Just as in SQL Server, Access Date/Time fields store both time and date,
regardless of what has been entered. Jet stores these values as Double
numbers, with the whole number portion representing the number of days
since the seed date, and the decimal representing the time of day (.5 =
noon)

Are you sure the time values are not corresponding with the character
and numeric entries to make it appear that they were being sorted in
descending order? For example, with your actual results:

3/1/0719:50....CD12
3/1/0713:30....CC12
3/1/07 7:30 ....CB12
2/28/07 23:00....BB13
2/28/07 8:30....AA12
2/27/07 16:25....AA12
2/27/071:30....AA10

This is the correct sort order.


Steve Bottoms wrote:
Bob, thanks for responding!

Well, I did a little more digging. The query performs the exact same
way in Access 2k7's Query Builder: incorrect sort order. Now, the
date field in question contains a DATETIME value, and not JUST a date
value. When I replaced all of the DATETIME values with just DATE
values, the query started working correctly (Access and ASP)!

It looks like a bug to me: use DATETIME values, and Order By doesn't
work correctly. Use a DATE value, and the Order By works correctly!

Curious, no? Thanks!
Steve


"Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx> wrote in message
news:OoyKKg6aHHA.4312@xxxxxxxxxxxxxxxxxxxxxxx

When you run the same query in the Access environment (Access Query
Builder), do you get the correct results? If so, we need to
investigate the ASP end of things. I will need to set up a test
database and try to reproduce your result. It's been a long time
since I worked with Access, but I recall that the the order by
modifiers always worked correctly for me.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.