Sorting ADO recordset text field as numeric

Question

Using VBA i have a set of functions that return an ADODB.Recordset where all the columns as adVarChar. Unfortunately this means numerics get sorted as text. So 1,7,16,22 becomes 1,16,22,7

Is there any methods that can sort numerics as text columns without resorting to changing the type of the column?

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 am using Project 2003 and Excel 2003 and referencing Microsoft ActiveX DataObject 2.8 Library

Answer 1

Left pad with Zeros with at least as many as maximum number digits. e.g.

0001 0010 0022 1000

You can use Right$() to accomplish this.

Answer 2

Use the Val() function to sort numerically on a text column. Example:

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