Sorting Alpha numeric data and keeping the number sequence

  • Thread starter Thread starter JH
  • Start date Start date
J

JH

Is there a way to sort alpha numeric data in access and keep the numbers in
sequence? eg. data in field: F1, F2, F10, F120. when I generate a report
which groups on this field the order is: F1, F10, F120, F2 etc..

I have done a test where I separated the text and numbers in 2 different
fields and sort by the number alone. I later joined the two fields on the
report to display as one and this works out fine. However, it would mean that
I would have to manually modify over 400 records to separate the fields.

Please! someone!, tell me there's an easier way
 
I would have to manually modify over 400 records to separate the fields.
You do not need to do it manually if your data follows patterns.
Add a calculated field like this --
MySort: IIF(Left([YourField], 1) IsNumber, Format([YourField], "0000"),
Left([YourField], 1) & Format(Replace([YourField], Left([YourField],
1),""),"0000"))

This will handle data fields with or without letter as first character and
maximum of four characters.
 
If there is always one leading letter in the value, and no Nulls, do as Roger
says. If there can be a variable number of leading letters (including none
at all) add the following function to a standard module in the database:

Public Function SortVal(varVal As Variant) As Long

Dim n As Integer
Dim strTemp As String

If Not IsNull(varVal) Then
strTemp = varVal
For n = 1 To Len(strTemp)
strTemp = Mid(strTemp, n)
If IsNumeric(strTemp) Then
SortVal = CLng(strTemp)
Exit For
End If
Next n
End If

End Function

Create the following field in the query to sort by:

seq: SortVal([YourFieldName])

Ken Sheridan
Stafford, England
 
Roger, you are an "Angel". This worked fabulously! Thank you tonnes!

Roger Carlson said:
In your query, create a field something like this:

seq: CLng(Mid([field1],2))

Then sort on that field in your report.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



JH said:
Is there a way to sort alpha numeric data in access and keep the numbers
in
sequence? eg. data in field: F1, F2, F10, F120. when I generate a report
which groups on this field the order is: F1, F10, F120, F2 etc..

I have done a test where I separated the text and numbers in 2 different
fields and sort by the number alone. I later joined the two fields on the
report to display as one and this works out fine. However, it would mean
that
I would have to manually modify over 400 records to separate the fields.

Please! someone!, tell me there's an easier way
Click to expand...
Click to expand...
 
You're quite welcome. Bear in mind that if your data ever has more than one
character in front, Ken's solution is more general.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


JH said:
Roger, you are an "Angel". This worked fabulously! Thank you tonnes!

Roger Carlson said:
In your query, create a field something like this:

seq: CLng(Mid([field1],2))

Then sort on that field in your report.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



JH said:
Is there a way to sort alpha numeric data in access and keep the
numbers
in
sequence? eg. data in field: F1, F2, F10, F120. when I generate a
report
which groups on this field the order is: F1, F10, F120, F2 etc..

I have done a test where I separated the text and numbers in 2
different
fields and sort by the number alone. I later joined the two fields on
the
report to display as one and this works out fine. However, it would
mean
that
I would have to manually modify over 400 records to separate the
fields.

Please! someone!, tell me there's an easier way
Click to expand...
Click to expand...
Click to expand...
 
Back
Top