Sorting records by values that contain numeric and alpha characters

I am trying to sort records when i reterive them using a SQL statement using visual basic.  The values can sometimes contain both alpha a numeric characters.  An example of these values would be.

1009
110
175
35BC
35S
40B
45
55

I would like them to be sorted as follows based on the numeric value.  

35BC
35S
40B
45
55
1009
110
175

Any way of accomplishing this.  The SQL statement i am using is:
"SELECT DISTINCT tblClassRates.ClassID From tblClassRates ORDER BY tblClassRates.ClassID;"

I have tried some of the examples found here with no success.

The database where the records is stored is an MS Access database.  

Any ideas?



Mike_StevensAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Try
"SELECT DISTINCT Right('     '+ tblClassRates.ClassID,5) as NewClassID From tblClassRates ORDER BY NewClassID;"

mlmcc
0
Mike_StevensAuthor Commented:
I get error -2147467259 "order by clause (newclassID) conflicts with distinct.   I suspect this is an ADO error

This is how i am opening the recordset:

        strSQL = "SELECT DISTINCT Right('     '+ tblClassRates.ClassID,5) as NewClassID From tblClassRates    ORDER BY NewClassID;"
             
        Set rsRate_Scales = New ADODB.Recordset
        rsRate_Scales.CursorLocation = adUseServer
        rsRate_Scales.Open strSQL, adoConnection(1), adOpenKeyset, adLockPessimistic
        DoEvents
0
Bob LambersonSoftware EngineerCommented:
> SELECT DISTINCT tblClassRates.ClassID From tblClassRates ORDER BY
> tblClassRates.ClassID
Try
SELECT DISTINCT Val([tblClassRates].[ClassID]) AS Expr1
FROM tblClassRates
ORDER BY Val([tblClassRates].[ClassID]);


Bob
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike_StevensAuthor Commented:
Bob....It works without error but ignores any records that contain alpha characters.  Only returns records that contains numeric characters.
0
EDDYKTCommented:
SELECT tblClassRates.ClassID From tblClassRates ORDER BY val(tblClassRates.ClassID)
0
Mike_StevensAuthor Commented:
I need the DISTINCT in the SQL statement.  It does not sort the properly anyway.
0
EDDYKTCommented:
This will sort properly without distinct, I have to see how to do it because it is
conflict with val

ie you have two 35 after takes out the letters



SELECT tblClassRates.ClassID  From tblClassRates ORDER BY val(tblClassRates.ClassID), tblClassRates.ClassID
0
Mike_StevensAuthor Commented:
ok.  The sort, even though they are duplicated records is still not what i am looking for.
0
michaeldadeCommented:
I really dont think you can do this with a query , my suggestion is load the data into an array and do a quick sort

http://www.vb-helper.com/howto_quicksort.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.