By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

Sorting numbers in a Text field

P: 112
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

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?
Oct 10 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 3,072
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 ?

Oct 11 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.