Access 2000. Macro to add leading zero

[bigceedee]

I have a table with a column labeled company code (3rd column over) that when I append data records, does not insert the leading zero, for a 3-digit character, even though my column is a text column. I know Access considers this a placeholder but this leading zero is truly needed.l I want to create my macro to add one leading zero to all 2-digit codes in this column. How can this be done?
Bigceedee

 

[Joe4]

This can be done via an Update Query. PLEASE MAKE A COPY OF YOUR TABLE BEFORE YOU DO THIS, IN CASE OF A MISTAKE!

1. Create a new query, and add the "Code" field (I will call it "Code" for this example).

2. Then add this calculated field: Len(

)[/b]

3. Add this criteria to this calculated field: [b]<3[/b]
This will return all records where the Code is less than 3 characters long

4. Change the query type to "Update Query" (click on the icon of the Pencil with Exclamation point in the query toolbar, and select "Update Query")

5. You will see a new line labeled "Update To" in the query builder.  Under your "Code" field, enter this expression in the "Update To" line:
[b]Left("000",3-Len([Code])) & [Code][/b]

6. Click on the Run button (red exclamation point).  It will warn you that you are about to update records.  Select yes.  Your records should now be updated.

 

[Norie]

Jim

How about just using Format(

, "000") for the update?

 

[Joe4]

Jim

How about just using Format(

, "000") for the update?[/quote]
Yes, that would work also, and is a little shorter.

BTW, who is Jim??? :-?

 

[Norie]

BTW, who is Jim???

Sorry must have got you mixed up with someone else.