Sorting a subform with ascending values

[paulevans]

Hi All hope some one can help with this.

I have a sub for which is set to sort by ascending order for a given field which happens to be a text field with vales set something like abc1.1,abc1.2 ect. The problem is, is that when you get to abc1.10, and more these are then sorted as so
abc1.1
abc1.10
abc1.11
abc1.2
abc1.3
ect

How can I stop this and sort it:
abc1.1
abc1.2
abc1.3
.......
abc1.10
abc1.11

Thanks for any help with this
Paul

 

[jwhite]

With the data in the present form, the sort is working properly. You could:

1. Write code that would reformat the values on the fly.
Not a good choice, overhead.

2. Manually change the values - abc1.3 to abc1.03.
If you don't have too many records, this would be good.

3. If you have a gazillion records, are the values format consistant? Meaning, number sequence is always preceeded by five characters (abc1.)? If so, you could make an Update Query to replace the value with left(field1,5) + format(mid(field1,6),"000").

I would recommend 2 or 3. "Normalizing" your data format as soon as possible is the way to go. Click here for examples using the REPLACE command. And here is another good page for SQL Format and other functions.

You haven't stated what your data refers to. So, it may be right for you, or it may not...

 

[gemma-the-husky]

search the forum - ther's a lot of threads about sorting text values like this