sorting alpha numbers

S

shank

Is there a way to format the below field data so it will sort in numerical
order? i.e. PO 1, PO 2, PO3, etc.

PO 1
PO 12
PO 13
PO 15
PO 16
PO 17
PO 18
PO 19
PO 2
PO 20
PO 21
PO 22
PO 23
PO 3
PO 4
PO 5
PO 6
PO 7
PO 8
PO 9
 
A

Allen Browne

The best solution is not to put 2 different values (some text and a number)
into one field.

If all fields are PO, omit the PO and just add it to the label or text box
on your form or report.

If some fields will have another value, then the PO prefix probably
represents one of a range of meanings. As such, it should be given a field
of its own, to meet the basic normalization rule that all fields should be
atomic (only one thing in one field.)

If you have some good reason to break the rule, you could add leading zeros
so the numbers sort correctly.

And finally,the most inefficient of all solutions would be to parse the
field into text and number at runtime for sorting. The query would end up
like this:
ORDER BY Left([Field1], 2), Val(Mid([Field1], 4);
 
R

Rick B

If you change them to PO 01, PO 02, etc. would be one way

If you take out the "PO" part (which is not needed in the table) and change
the field to a number, would be another way.
 
C

Chaim

Use mid() to strip off the numeric part of the string and do an ORDER BY
that would look something like:

ORDER BY val(mid (<the name of the field>, Len ("PO ") );

This assumes there is nothing following the numeric part of the string.

Good Luck!
 
Top