Sorting Alphanumeric Text Fields

[Guest]

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.

 

[Joseph Meehan]

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 am sorry, but I don't understand your question. IF you want to sort
the field, why use the VAL function?

 

[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]

 

[Guest]

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.

 

[Joseph Meehan]

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?

 

[Joseph Meehan]

HilltopMG wrote:

Also again what are you using the VAL function for?

 

[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]

 

[Guest]

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?

 

[Guest]

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.

 

[Joseph Meehan]

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 message. It was not very responsive to your message. I
got carried away and had not properly read your message. I tried deleting
the messages, but that seems to have failed.

You likely know this, but there are several sorting orders with
different systems. Alpha is easy to figure out. Numbers some times come
before alpha and sometimes after depending on the method used (generally
mainframe and PC do not do this the same way. In addition numbers in a text
field are left to right according to each digit's value of 0-9 Numbers in a
number filed are sorted according to their numeric value of the whole
number.

I put off responding because I wanted to take the time to review what
you had written and tried to figure out how it all would have impacted on
your data and the results you wanted.

I will try and get back to this if I can before someone else answers it
for you. Right now I am soft of zoned out.

 

[Guest]

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