Update Query - Pad String with Leading Zeros

I am trying to write an update query that will convert all of my records in a field to 14 digits with leading zeros (0).

So, instead of
19750
745513265

I would like
00000000019750
00000745513265

In Excel, I do it using =Rept("0",14-Len(B1))& B1, but cannot figure out the syntax in Access.

This must be simple, but I am tearing my hair out.  Please help.  Thanks.
lstapleAsked:
Who is Participating?
 
nico5038Connect With a Mentor Commented:
You don't really need this as you can always use the format statement to "mimic" this behaviour.

But you can also use it to transform the field.
First add a text field as I assume it's now numeric..

Then use =format([fieldname],"00000000000000") in an update query to get the field transformed.

Nic;o)
0
 
lstapleAuthor Commented:
That did the trick!  Thank you so much; the answer had eluded me for several days.
0
 
nico5038Commented:
Then next time use EE sooner ;-)
I personally stop working on a problem when it takes longer as one hour. Then it's time to ask a collegue (or EE)

Success with the application !

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.