Access 2003 Sorting Text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BRich - 20 Feb 2008 16:11 GMT

I am looking for a way to order text numbers - similar to a number list order
in Word i.e. - 1,1.1, 1.2, 2, 2.1, 2.2, 3 ... etc. These are item numbers in
a procedure list that I want to show on a form or report in 'text' order.
The problem is that the sort order 'reads' 2.10 (two decimal ten) as equal to
2.1 (two decimal one). Is there a routine for handling this sort? Any help
would be appreciated.

Cheers,

Signature

BRich

Reply to this Message

NetworkTrade - 20 Feb 2008 16:26 GMT

am not sure this is the reply you are looking for....but it seems like the
field is defined as a number - if it dropping that 0; but could/should be
defined as a text field - - which would give you what you want

if the field never involves math - then change it's Table's data type
definition to a text field

seems too simple so maybe there is more to it....

Signature

NTC

> I am looking for a way to order text numbers - similar to a number list order
> in Word i.e. - 1,1.1, 1.2, 2, 2.1, 2.2, 3 ... etc. These are item numbers in
[quoted text clipped - 4 lines]
>
> Cheers,

Reply to this Message

Dale Fye - 20 Feb 2008 18:13 GMT

I've got a function I use to reformat the numbers from

1.1 to 1.01
and
1.10 to 1.10

Which will then sort properly. Unfortunately, it takes a while for this to
run with a large recodset.

Public Function Renum(SomeText As String) As String

Dim myArray() As String, intLoop As Integer
Dim myString As String

myArray = Split(SomeText, ".")
For intLoop = LBound(myArray) To UBound(myArray)
myString = myString & "." & Format(myArray(intLoop), "00")
Next
Renum = Mid(myString, 2)

End Function

You can use this in a query as a computed field, sort by it, but don't
include it in your output.

HTH
Dale

Signature

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Reply to this Message