Re: Simple SQL ORDER BY doesn't work!

Tech-Archive recommends: Fix windows errors by optimizing your registry

Steve Bottoms wrote:
Quite right: long day, frustrating problem.

Database= Access 2k3
Script = Classic ASP
Server = Windows 2k3 running IIS6 -- and --
Server = Windows Vista Business running IIS7

Code:
strQuery = "select
bLastUpdate,bBody,bNumber,bTitle,bURL,bUpdates,bFiscalNotes,id from
Bills where bStatus='Active' and bLastUpdate is not null order by
bLastUpdate desc,bBody asc,bNumber asc"
set objRS = objConnection.Execute(strQuery)

Fields:
bLastUpdate = smalldatetime (indexed, dupes OK)
bBody = string(5) (indexed, dupes OK)
bNumber = integer (non-negative) (indexed, dupes OK)

Actual results like this:
3/1/07....CD12

Is CD12 in the bBody field? Or is CD in bBody and 12 in bNumber?

3/1/07....CC12
3/1/07....CB12
2/28/07....BB13
2/28/07....AA12
2/27/07....AA12
2/27/07....AA10

Results should be something like this:
3/1/07....CB12
3/1/07....CC12
3/1/07....CD12
2/28/07....AA12
2/28/07....BB13
2/27/07....AA10
2/27/07....AA12

Basically, I'm trying to sort by the date field DESCENDING, and
within each unique day sort on bBody and bNumber ASCENDING. Tried
doing a string conversion on the numeric field and adding
(concatenating) it to the string field; no effect. Tried using CAST
and CONVERT (transact sql functions); don't work with Access
apparently.

Nope - you have to use VBA functions (CStr, etc.) - this is one of the
things that makes ti difficult to port between Access and SQL Server.

Doesn't matter whether I use the ASC modifier or not on
the second and third field names.
Seems so cut and dry; similar queries work perfectly with SQL Server,
so I'm wondering if it's an Access driver bug. Any suggestions
welcome.

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. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.