the Manx Man's MS Access Pages

My Order Numbers Don't Sort Properly (012)

I dealt with a client who had started an Access data base and then got overwhelmed. They asked me to come in and finish it up.

One problem they had run into was a crazy sort pattern on their purchase order numbers. Each department had an alpha character followed by a sequential number. So, for example, the warehouse POs were entered like this:

W1,   W2,   W3,   W4,   . . .    W10,   W11,   . . .

but when listed on a report they went like this:

W1,   W10,   W11,   W2,   . . .

Access is not smart enough to handle this pattern. Because the 'W' is an alpha character the whole thing is treat that way and alpha sorts are left to right. The '1' in 'W10' comes before the '2' in 'W2'.

To fix this, I made the pattern like this, 'Wnnnn', where the 'n' is a digit. This allows for 9,999 purchase orders, plenty for this company. However, how can we get Access to automatically generate the next number? I can use ("W" & (right([ponum],4)+1)) to get the next number, but if I use that technique on 'W0015' I will get 'W16'. To fix it, use this technique.

           "W" & right("0000" & (right([ponum],4)+1),4)

Tip013 gives a generic view of this situation, Adding Zeroes to the Start of a Numeric Field.