Sorting Text Fields Containing Letters and Numbers

[code_hungry]

To whom it may confuse. I have a query with a text field that needs to be
sorted in descending order. The field name is "ContainerNumber" and the data
will always have at least a letter in the begining. As you can see the data
sample "D100/07" comes after "D10/07" and that is not what I need. I need
"D11/07" to come after "D10/07" and so on. How can I fix this?

ContainerNumber
D03/07
D06/07
D07/07
D08/07
D09/07
D10/07
D100/07
D101/07
D102/07
D103/07
D104/07
D105/07
D106/07
D107/07
D108/07
D109/07
D11/07
D110/07
D111/07
D112/07
D113/07
D114/07
D115/07
D116/07
D117/07
D118/07
D119/07
D12/07
D120/07
D121/07
D13/07
D14/07
D15/07
D16/07
D17/07
D18/07
D19/07
D20/07
D21/07
D22/07
D23/07
D24/07

 

[Ken Snell \(MVP\)]

Here's a function that I use for such purposes, and it may be useful "as is"
for your needs; use it for a calculated field in the query and sort on it.

SELECT *, ReturnSortValueForAlphaNumerics([ContainerNumber]) As SortField
FROM YourTableName
ORDER BY ReturnSortValueForAlphaNumerics([ContainerNumber]);




Public Function ReturnSortValueForAlphaNumerics(ByVal strOriginal) As String
' ** LOGIC IS TO REPLACE EACH CHARACTER IN THE ORIGINAL STRING WITH A MULTI-
' ** CHARACTER "NUMBER" STRING THAT WILL SORT THE ORIGINAL STRING CORRECTLY.
Dim lngLoc As Long
Dim strSort As String, strT As String, strLoc As String

Const strDash As String = "-"
Const strNum As String = "[0-9]"


lngLoc = 1
strT = Left(strOriginal, 1)
strSort = Format(Abs(Not strT Like strNum) & IIf(IsNumeric(strT), "00",
Asc(strT)), "000")
strT = ""

Do
strLoc = Mid(strOriginal, lngLoc, 1)
If strLoc Like strNum Then
Do
strT = strT & strLoc
lngLoc = lngLoc + 1
strLoc = Mid(strOriginal, lngLoc, 1)
Loop While strLoc Like strNum
strSort = strSort & Right("!!!!!!!!!!" & CStr(Val(strT)), 10)
strT = ""

Else
If strLoc = strDash Then
strSort = strSort & "AAA"
Else
strSort = strSort & strLoc & "ZZ"
End If
lngLoc = lngLoc + 1
End If

Loop Until lngLoc > Len(strOriginal)

ReturnSortValueForAlphaNumerics = strSort

End Function

 

[Marshall Barton]

To whom it may confuse. I have a query with a text field that needs to be
sorted in descending order. The field name is "ContainerNumber" and the data
will always have at least a letter in the begining. As you can see the data
sample "D100/07" comes after "D10/07" and that is not what I need. I need
"D11/07" to come after "D10/07" and so on. How can I fix this?

You can use a public function in a standard module to
calculate an sortable string. Here's some air code with the
general idea:

Public Function ContNum(CN As String) As String
Dim pos As Integer

For pos = 1 To Len(CN)
If Mid(CN, pos, 1) Like "[0-9]" Then Exit For
Next pos
ContNum = Left(Left(CN, pos - 1) & String(" ", 7), 7)

ContNum = ContNum & Format(Val(Mif(

 

[Marshall Barton]

Oops,

to continue:

Public Function ContNum(CN As String) As String
Dim pos As Integer

For pos = 1 To Len(CN)
If Mid(CN, pos, 1) Like "[0-9]" Then Exit For
Next pos
ContNum = Left(Left(CN, pos - 1) & Space(7), 7)

ContNum = ContNum & Format(Val(Mid(CN, pos)), String("0",8))

Pos = InStr(CN, "/") + 1
ContNum = ContNum & Format(Val(Mid(CN, pos), String("0",6))

End Function

Then sort on the calculated field:
ContNum(ContainerNumber)

 

[raskew via AccessMonster.com]

Marshall:

Re last line: You haven't closed the Val() function. Believe it should read:


ContNum = ContNum & Format(val(Mid(CN, pos)), String("0", 6))

Bob

Oops,

to continue:

Public Function ContNum(CN As String) As String
Dim pos As Integer

For pos = 1 To Len(CN)
If Mid(CN, pos, 1) Like "[0-9]" Then Exit For
Next pos
ContNum = Left(Left(CN, pos - 1) & Space(7), 7)

ContNum = ContNum & Format(Val(Mid(CN, pos)), String("0",8))

Pos = InStr(CN, "/") + 1
ContNum = ContNum & Format(Val(Mid(CN, pos), String("0",6))

End Function

Then sort on the calculated field:
ContNum(ContainerNumber)

 

[Marshall Barton]

Marshall:

Re last line: You haven't closed the Val() function. Believe it should read:


ContNum = ContNum & Format(val(Mid(CN, pos)), String("0", 6))

Right!