If you need a numeric field with leading zeroes, do this. (I assume integer or long integer)

1. Change the field to a text field, the correct size. MS Access will convert the data from numbers to text fine.
2. Create an update query with the field in it. In the "Update To" cell, put a formula like this:
       right(("0000" & [original_field_name]),4)
           or
       format([original_field_name],"0000")
   This assumes you want a field 4 characters in size. Modify to suit.

When does this matter?

As an application of the above technique, consider the following complaint of, "My Order Numbers Do Not Sort Properly".

(article continues after sponsor spot)

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, . . . 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 string is treated 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)
        or, its equivalent using the format function,
    "W" & format((cint(right([ponum],4))+1),"0000")

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved.