Sorting Alpha numeric data and keeping the number sequence

[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

 

[Roger Carlson]

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

 

[KARL DEWEY]

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.

 

[Ken Sheridan]

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

 

[JH]

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

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

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

 

[Roger Carlson]

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

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

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

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