sort text field with numbers and letters

[LUIS-PR]

Hi, I know there has been a lot of asking about this but I have a hard time
dealing with it

Problem: Text field wich contains letters and numbers, only letters or only
numbers..
EJ.
1
2BC
A
Z
11
12
15
N

I know that i can use the val function to sort numbers but when I do that
letters will be shown all mix up like;

Z
B
A
C

then the numbers perfectly sort but I need to sort them all, If that is
posibble?

 

[Jeff Boyce]

Access can handle "sort-alphabetically" or "sort-numerically".

If you mix them up, Access is mixed up.

You will have to create a procedure that performs the sort exactly as you
would, and you'll have to be explicit.

All that said, what do those letters and digits represent? Why are they all
mixed up together?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Hi, I know there has been a lot of asking about this but I have a hard time
dealing with it

Problem: Text field wich contains letters and numbers, only letters or only
numbers..
EJ.
1
2BC
A
Z
11
12
15
N

I know that i can use the val function to sort numbers but when I do that
letters will be shown all mix up like;

Z
B
A
C

then the numbers perfectly sort but I need to sort them all, If that is
posibble?

What is the desired sort order? Numbers first then A to Z? A to Z followed by
numbers? or what?

Note also that the val() function will return zero if the string doesn't start
with a digit. Val("A12") is in fact 0, not 12.