Sorting numbers in a Text field

[Redbeard]

I have a text field that I used to store the unique number of each record. I wish to sort this number but since it is in a text field it will not sort numerically. I have the number in a text field because it can but not always contain letters. For example

1950.01.01 a-b
1960.21.100
X1980.504.25
2002.05.01.01-08

It now sort the field from left to right digit by digit. I need it to sort the field by number from left to right. For example

1980.01.01 = "1980" . "01" . "01"

Is there any way of doing this?

[nico5038]

One option is to create a function that changes your string into a fixed length string with just numbers and leading zero's.
For this you need to know the max number of numbers. Lets assume 12 (seeing your last sample)
Now the function will have to extract all 0 to 9 characters and place 12 zero's infront of it. Then a right("000000000000" & strDigits,12) will give the fixed length string and that string can be sorted alphabetically returning the correct sequence.

Getting the idea ?

Nic;o)