Sorting Alphanumeric Text Fields

[HilltopMG]

I have inherited an Access 97 database that has a field which is defined as
text. It contains a Package ID number but some of the older numbers contain
alpa characters as well as * or ~. I have read some of the posts here, but
have not been successful. The Val function gives the data mismatch in
criteria expression message. I am not familiar with this database but know
that proper sorting of this field is the key here. The field can contain
data of varying lengths.

Any help would be greatly appreciated.

 

[John Vinson]

I have inherited an Access 97 database that has a field which is defined as
text. It contains a Package ID number but some of the older numbers contain
alpa characters as well as * or ~. I have read some of the posts here, but
have not been successful. The Val function gives the data mismatch in
criteria expression message. I am not familiar with this database but know
that proper sorting of this field is the key here. The field can contain
data of varying lengths.

Any help would be greatly appreciated.

I'm puzzled too.

Please post some examples of such Package ID numbers and indicate how
you would like them sorted, and please also post the SQL of the query
that's giving you the data mismatch error message.

John W. Vinson[MVP]

 

[HilltopMG]

Sorry for the confusion, new to posting.

Here's the SQL statement being used:

SELECT DISTINCTROW tblPackaging.*, tblPackaging.PAKNo
FROM tblPackaging
ORDER BY Left([PAKNO],2), IIf(IsNumeric(Left([PAKNo],7)),IIf(Not
IsNumeric(Right([PAKNo],1)),1,IIf(Len([PAKNo])=7,100,1)),1),
tblPackaging.PAKNo;

Some sample data looks like this:
609990
6000010
6000020
..
..
6001120
6001130
6001140
610010
610020
610030
..
..
..
a*1000-11
a*1000-12
..
..
..
a~1PK018
a~1PK036
..
..
..

Hope you can help me.

Thanks.

 

[Ken Snell [MVP]]

Perhaps you could call the following function in a calculated field (
MySortField: ReturnSortValueForAlphaNumerics([FieldName]) ) in your query,
and do Ascending sort on this calculated field:


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.
' Ken Snell :: April 26, 2007

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>

Sorry for the confusion, new to posting.

Here's the SQL statement being used:

SELECT DISTINCTROW tblPackaging.*, tblPackaging.PAKNo
FROM tblPackaging
ORDER BY Left([PAKNO],2), IIf(IsNumeric(Left([PAKNo],7)),IIf(Not
IsNumeric(Right([PAKNo],1)),1,IIf(Len([PAKNo])=7,100,1)),1),
tblPackaging.PAKNo;

Some sample data looks like this:
609990
6000010
6000020
.
.
6001120
6001130
6001140
610010
610020
610030
.
.
.
a*1000-11
a*1000-12
.
.
.
a~1PK018
a~1PK036
.
.
.

Hope you can help me.

Thanks.

I'm puzzled too.

Please post some examples of such Package ID numbers and indicate how
you would like them sorted, and please also post the SQL of the query
that's giving you the data mismatch error message.

John W. Vinson[MVP]

 

[HilltopMG]

I'd like all numeric data to be srted by their value so the 6 digit numbers
would come first and the 7 digit numbers would follow. i would assume the
alphanumerics would follow after that.

Sorry for the confusion, new to posting.

Here's the SQL statement being used:

SELECT DISTINCTROW tblPackaging.*, tblPackaging.PAKNo
FROM tblPackaging
ORDER BY Left([PAKNO],2), IIf(IsNumeric(Left([PAKNo],7)),IIf(Not
IsNumeric(Right([PAKNo],1)),1,IIf(Len([PAKNo])=7,100,1)),1),
tblPackaging.PAKNo;

Some sample data looks like this:
609990
6000010
6000020
.
.
6001120
6001130
6001140
610010
610020
610030
.
.
.
a*1000-11
a*1000-12
.
.
.
a~1PK018
a~1PK036
.
.
.

Hope you can help me.

Thanks.

OK and in what order do you want it sorted and how does it not do it
now?

 

[HilltopMG]

I was trying the VAL function because I thought it would return the value of
the field as I had read in several other examples related to sorting
alphanumeric text fields.

 

[Richard Mueller]

Based on Ken's function, some more ideas.

1. Pad the input string with leading zeros, in case the strings are numeric,
so they will sort correctly as numbers. You will have to specify a max
length, I made it 10 characters below. Then, convert each character to an
ascii value, convert each ascii value to 3 character string with leading
zeros. The result will be 30 characters, but numeric values will sort
correctly. For example:

Private Function AlphaSort(ByVal strOriginal As String) As String
' Function to convert character string to a string of 3 digit Ascii
values.
' Input string is limited to 10 characters.
' Result is 30 characters, but will sort correctly.

' Pad string with leading zeros (Maximum 10 characters).
strOriginal = Right("0000000000" & Trim(strOriginal), 10)
AlphaSort = ""
' Convert each character of input string to an Ascii value,
' convert this value to a 3 character string with leading zeros,
' and append to output string.
Do Until (Len(strOriginal) = 1)
AlphaSort = AlphaSort & Right(CStr("000" & Asc(Left(strOriginal,
1))), 3)
strOriginal = Mid(strOriginal, 2)
Loop
AlphaSort = AlphaSort & Right(CStr("000" & Asc(strOriginal)), 3)

End Function

2. If the input string is numeric, use the Format function to convert to a
string with leading zeros. If not numeric, pad with leading characters like
"Z" so they sort after numbers. Numeric values with dollars and/or commas
will format to straight numbers, but anything after any decimal points will
be ignored. For example:

Private Function AlphaSort2(ByVal strOriginal As String) As String
If (IsNumeric(strOriginal) = True) Then
AlphaSort2 = Format(strOriginal, "0000000000")
Else
AlphaSort2 = Right("ZZZZZZZZZZ" & Trim(strOriginal), 10)
End If
End Function

Richard Mueller
Microsoft MVP ADSI and VBScript