• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

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?



0
Mike_Stevens
Asked:
Mike_Stevens
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now