Access 2003 Sorting Text

[BRich]

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,

 

[NetworkTrade]

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....

 

[Dale Fye]

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