Number Sorting

[ehorde]

I have a subform that is set to display as continuous and one of the fields I sort on is a text field that contains single and double digit numbers.

ie..

1
2
3
.
.
23
24
etc..

The problem is that 23 sorts before 3.

In this form I cannot force leading zeros, it is not an option as 023 may not be a valid piece of data, but 23 may.

I can also have A23 or 23A, just as I can have A3 or 3A, and I would need 3A to sort before 23A.

Any one have any pointers for me on this one?

As always, thanks in advance.

 

[gemma-the-husky]

if you have alphanumerics, the values are text and the "word" 23 DOES come before the "word" 3

what you are trying to do is like having words "BC" and "C", and expecting "C" to sort ahead of "BC"

The ONLY reliable way of doing this is to have all strings of equvalent lengths (say 3chars)

then you have

A01
002
023

etc, and you dont get any sorting problems. It depends what you are trying to represent though.

 

[namliam]

Different subject, but scroll down and find my explenation why... and how to fix it a little down the thread...

http://www.access-programmers.co.uk/forums/showthread.php?t=172772&highlight=Sort

Edit: Cross posted with GTH, while searching for my above thread