Sorting ADO recordset text field as numeric
ado, adodb, vba
Solution
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.
Problem
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