VB Forum / Database Access / July 2008

I don't know of an SQL solution, i.e. a "smart sort", but sorting numbers
represented as strings is a common problem.  The most common
solution is to left justify the numbers so they sort properly.

For example the series 1,2,3,10,11 (as strings) will sort as 1,10,11,2,3.
But
if the numbers are changed to __1, __2, __3, _10, _11 (where the underscore
is a space, they will sort properly.

A short VB example with a sorted listbox might be:

Private Sub Form_Load()
Dim i As Long
For i = 0 To 100
 List1.AddItem LJust(i, 3)
Next
End Sub
Private Function LJust(ByVal Item As String, ByVal Length As Long) As String
LJust = Right$(Space$(Length) & Item, Length)
'or
'LJust = Format$(Item, String$(Length, "@"))
End Function

In short, you are going to have to reformat/edit your ADDR field
so it will sort properly

Here is a function that attempts to left justify the numbers in an
address.

Private Function LJustAddr(ByVal Addr As String)
'assumes the number comes first, followed by a space
Dim NewNum As String
Dim OldNum As String
OldNum = Split(Addr)(0)
NewNum = LJust(OldNum, 5) '5 digit house number
LJustAddr = Replace$(Addr, OldNum, NewNum)
End Function

Air code to edit your field (DAO)
For i = 1 to RS.RecordCount
RS.Edit
RS.Fields("ADDR").Value = LJustAddr(RS.Fields("ADDR").Value)
RS.Update
RS.MoveNext
Next