Sort the text field of ADO records as numeric

Question

Using VBA, I have a set of functions returning ADODB.Recordsetwhere all the columns are like adVarChar. Unfortunately, this means that the numerators are sorted as text. Thus 1,7,16,22 becomes 1,16,22,7

Are there any methods that can sort numbers as text columns without resorting to changing the column type?

Sub TestSortVarChar()

  Dim strBefore, strAfter As String

  Dim r As ADODB.RecordSet

  Set r = New ADODB.RecordSet
  r.Fields.Append "ID", adVarChar, 100
  r.Fields.Append "Field1", adVarChar, 100
  r.Open


  r.AddNew
  r.Fields("ID") = "1"
  r.Fields("Field1") = "A"

  r.AddNew
  r.Fields("ID") = "7"
  r.Fields("Field1") = "B"

  r.AddNew
  r.Fields("ID") = "16"
  r.Fields("Field1") = "C"

  r.AddNew
  r.Fields("ID") = "22"
  r.Fields("Field1") = "D"


  r.MoveFirst
  Do Until r.EOF
    strBefore = strBefore & r.Fields("ID") & " " & r.Fields("Field1") & vbCrLf
    r.MoveNext
  Loop

  r.Sort = "[ID] ASC"


  r.MoveFirst
  Do Until r.EOF
    strAfter = strAfter & r.Fields("ID") & " " & r.Fields("Field1") & vbCrLf
    r.MoveNext
  Loop

  MsgBox strBefore & vbCrLf & vbCrLf & strAfter

End Sub

NB: I use Project 2003 and Excel 2003 and reference the Microsoft ActiveX DataObject 2.8 Library

Answer 1

Left panel with zeros, at least with maximum digit numbers. eg.

0001 0010 0022 1000

You can use Right $ () to accomplish this.

Answer 2

Val() . :

SELECT ID, Field1
FROM tablename
ORDER BY Val(Field1);