Sorting with numbers in the middle of a text string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table ("tblExample") of property information. Property is identified
by discreet serial numbers located in the "strData" field. Because other
people are going to use the database, I prefer not to separate the serials
into three fields to hold prefixes, numbers, and suffixes.

The data looks like this:

NMC10000
NMC10001
NMC9998
NMC9999
NMC9999a

and I would like it to sort like this:

NMC9998
NMC9999
NMC9999a
NMC10000
NMC10001.

I've tried mid(), inStr(), etc, but I can't seem to make anything work. Any
suggestions?
 
coxrail said:
I have a table ("tblExample") of property information. Property is identified
by discreet serial numbers located in the "strData" field. Because other
people are going to use the database, I prefer not to separate the serials
into three fields to hold prefixes, numbers, and suffixes.

The data looks like this:

NMC10000
NMC10001
NMC9998
NMC9999
NMC9999a

and I would like it to sort like this:

NMC9998
NMC9999
NMC9999a
NMC10000
NMC10001.

I've tried mid(), inStr(), etc, but I can't seem to make anything work. Any
suggestions?
Click to expand...

I forgot to say that there is NO pattern of prefix character length. In
fact, there may not be any prefix.
 
Here is a function that I've written to do this type of sorting. Use it as a
calculated field in your query (with the field that contains the values to
be sorted as its argument) and sort on the calculated field. Put the
function in a regular module.

Public Function ReturnSortValueForAlphaNumerics(ByVal strOriginal) As String
' Written by K. D. Snell - May 18, 2005
' ** 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
 
Works like a champ. I sincerely thank you for your help!

Ken Snell (MVP) said:
Here is a function that I've written to do this type of sorting. Use it as a
calculated field in your query (with the field that contains the values to
be sorted as its argument) and sort on the calculated field. Put the
function in a regular module.

Public Function ReturnSortValueForAlphaNumerics(ByVal strOriginal) As String
' Written by K. D. Snell - May 18, 2005
' ** 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

--

Ken Snell
<MS ACCESS MVP>

coxrail said:
I forgot to say that there is NO pattern of prefix character length. In
fact, there may not be any prefix.
Click to expand...
Click to expand...
 
Back
Top