Sorting mixed alphanumeric data

  • Thread starter Thread starter coxrail
  • Start date Start date
C

coxrail

Can someone help me out with the string sorting?

I have a query that sorts data alphabetically like this:
N1456
N2
P12345
P1500

I would like to see the data ordered like this:
N2
N1456
P1500
P12345

Thanks
 
Can someone help me out with the string sorting?

I have a query that sorts data alphabetically like this:
N1456
N2
P12345
P1500

I would like to see the data ordered like this:
N2
N1456
P1500
P12345

Thanks
Click to expand...

Not enough information.
Sort the data in the query?
Does the numeric value always start at the second character spot?
Add 2 new columns in this order:
SortLetter:Left([FieldName],1)
SortNumber:Val(Mid(FieldName],2))

Sort according to these new columns. Make sure the SortLetter column
is to the left of the SortNumber column.

Sort the data in a Report?
In Report Design View, click on View + Sorting and Grouping.
Enter SortLetter and SortNumber (in that order) in the
Field/Expression column.
 
coxrail said:
Can someone help me out with the string sorting?

I have a query that sorts data alphabetically like this:
N1456
N2
P12345
P1500

I would like to see the data ordered like this:
N2
N1456
P1500
P12345
Click to expand...


It looks like you want to sort by the length of the field.

Add a calculated field to the query:

SortField: Len([the data field])

and select Ascending in the sort box.
 
Thanks for your reply. Let me illuminate.

I am trying to sort serial numbers. Serial numbers can be any number of
characters, but usually fewer than ten. The only identifiable rules of serial
number construction are that alpha characters, if present, always start the
strings. There may be 0 to 6 alpha characters. Numeric data can start in the
first to seventh position.

The crucial idea is for the code to first sort the alpha characters in
ascending order (M before N, M before MA) and then numbers in ascending order
(N2 before N12)

I would prefer the sort to work in a query. If not possible to do in SQL, I
could use VBA in a module.

fredg said:
Can someone help me out with the string sorting?

I have a query that sorts data alphabetically like this:
N1456
N2
P12345
P1500

I would like to see the data ordered like this:
N2
N1456
P1500
P12345

Thanks
Click to expand...

Not enough information.
Sort the data in the query?
Does the numeric value always start at the second character spot?
Add 2 new columns in this order:
SortLetter:Left([FieldName],1)
SortNumber:Val(Mid(FieldName],2))

Sort according to these new columns. Make sure the SortLetter column
is to the left of the SortNumber column.

Sort the data in a Report?
In Report Design View, click on View + Sorting and Grouping.
Enter SortLetter and SortNumber (in that order) in the
Field/Expression column.
Click to expand...
 
You will need two additional fields. First field is string (text) and
the other is integer.

NA1456 NA 1456
NA2 NA 2
N1456 N 1456
P12345 P 12345
P1500 P 1500

Sorted would be:

N1456 N 1456
NA2 NA 2
NA1456 NA 1456
P1500 P 1500
P12345 P 12345


Andy
Thanks for your reply. Let me illuminate.

I am trying to sort serial numbers. Serial numbers can be any number of
characters, but usually fewer than ten. The only identifiable rules of serial
number construction are that alpha characters, if present, always start the
strings. There may be 0 to 6 alpha characters. Numeric data can start in the
first to seventh position.

The crucial idea is for the code to first sort the alpha characters in
ascending order (M before N, M before MA) and then numbers in ascending order
(N2 before N12)

I would prefer the sort to work in a query. If not possible to do in SQL, I
could use VBA in a module.

:

Can someone help me out with the string sorting?

I have a query that sorts data alphabetically like this:
N1456
N2
P12345
P1500

I would like to see the data ordered like this:
N2
N1456
P1500
P12345

Thanks
Click to expand...
Not enough information.
Sort the data in the query?
Does the numeric value always start at the second character spot?
Add 2 new columns in this order:
SortLetter:Left([FieldName],1)
SortNumber:Val(Mid(FieldName],2))

Sort according to these new columns. Make sure the SortLetter column
is to the left of the SortNumber column.

Sort the data in a Report?
In Report Design View, click on View + Sorting and Grouping.
Enter SortLetter and SortNumber (in that order) in the
Field/Expression column.
Click to expand...
Click to expand...
 
Thanks for your reply. Let me illuminate.

I am trying to sort serial numbers. Serial numbers can be any number of
characters, but usually fewer than ten. The only identifiable rules of serial
number construction are that alpha characters, if present, always start the
strings. There may be 0 to 6 alpha characters. Numeric data can start in the
first to seventh position.

The crucial idea is for the code to first sort the alpha characters in
ascending order (M before N, M before MA) and then numbers in ascending order
(N2 before N12)
Click to expand...

But N2154 before NA anything... right?

You will indeed need some custom VBA code (or possibly some VERY snarky nested
IIF and InStr functions...)

Try copying the following into a new module and sorting by
AlphaPart([serialno]) and then by NumPart([serialno]):

Public Function AlphaPart(strIn As Variant) As String
Dim iPos As Integer
AlphaPart = ""
For iPos = 1 To Len(strIn & "") ' step through input string
If IsNumeric(Mid(strIn, iPos, 1)) Then
Exit Function
Else
AlphaPart = AlphaPart & Mid(strIn, iPos, 1)
End If
Next iPos
End Function

Public Function NumPart(strIn As Variant) As Integer
Dim iPos As Integer
NumPart = 0 ' default if no numeric part
For iPos = 1 To Len(strIn & "")
If IsNumeric(Mid(strIn, iPos, 1)) Then
NumPart = Val(Mid(strIn, iPos))
Exit Function
End If
Next iPos
End Function
 
Back
Top