How do I get an alphanumeric ID to sort correctly?

[Guest]

I have a field that contains alphanumeric entries and would like to sort this
properly.
The entries are as follows: ZS-SPF,Z-WKU,N100BB,N10MB
The 1st problem is that sorting it Ascending the "-" is ignored and the
fields are sorted as ZS-SPF, Z-WKU,.... when it should be Z-WKU, ZS-SPF,....
The 2nd problem is with the numbers aren't sorted properly, N100BB should be
after N10MB and not before.
There's no patern for the entries, meaning it can be only numerical, or
alpha, or both.
Can someone help me with this problem?

 

[Guest]

Hi Ricardo,

IF there's no pattern to it as you say, then you will have a difficult time
making it sort in the fashion that you want it to...

One thing that I might suggest is to first sort by the letters to the left
of the first dash it comes to, similar to this:

Left([FIELDWITHDASHES],InStr(1,[FIELDWITHDASHES],"-")-1)

then sort by the entire field, or you could try to be tricky and do a mid
from where the first dash is found to the next dash, or to the end of the
field...

As for the numbers... good luck!! If you desperately need it to search
"correctly" you could write a function that returns the numeric portion of
the text and sort on that, but it will make it quite slow if you have a lot
of data...

Hope that helps.

Damian.

 

[Joseph Meehan]

I have a field that contains alphanumeric entries and would like to
sort this properly.
The entries are as follows: ZS-SPF,Z-WKU,N100BB,N10MB
The 1st problem is that sorting it Ascending the "-" is ignored and
the fields are sorted as ZS-SPF, Z-WKU,.... when it should be Z-WKU,
ZS-SPF,.... The 2nd problem is with the numbers aren't sorted
properly, N100BB should be after N10MB and not before.
There's no patern for the entries, meaning it can be only numerical,
or alpha, or both.
Can someone help me with this problem?

I believe the sort order is:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` {
| } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
strings are the same except for a hyphen, the text with the hyphen is sorted
last.

There is no one sort order. Main frames commonly use one and PC's
commonly use another and there are others and sub verities.

This can cause some misunderstandings when sorting.

 

[Patrice]

The problem looks like the field contains what *you* see as multiple parts.
Of course the application doesn't have this knowledge and treats those
values as a whole. You could either split these fields yourself to be able
to sort of on each individual component or store each individual part in its
own column so that it is sorted as you wish.

 

[Jamie Collins]

There is no one sort order. Main frames commonly use one and PC's
commonly use another and there are others and sub verities.

There has even been two sort orders in Jet. See:

PRB: Sort Order Has Changed with Microsoft Jet version 4.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;q236952

Jamie.

--

 

[Ken Snell \(MVP\)]

I've used this function in some applications for sorting hypen-embedded
strings. Add a calculated field to your query, and use the ID field as the
argument of this function call; sort on this calculated field. See if it
gets you closer

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

 

[Joseph Meehan]

There has even been two sort orders in Jet. See:

PRB: Sort Order Has Changed with Microsoft Jet version 4.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;q236952

Jamie.

Thanks.