pad left text field

[carol]

pls advise how i would go about adding a specified
character to the left of a text field so that all entries
are 6 characters in length. example....current entries as
follows: 678, 3456, 123456 and need to be updated to
000678, 003456, 123456. would like to accomplish this in
an update query. thx.

 

[Tom Ellison]

Dear Carol:

One way to do it that I prefer:

RIGHT("000000" & YourValue, 6)

Add 6 zeros on the left, then take the right-most 6 characters.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Dejan]

You can use Format function:

Format (strSomeString, "000000")
If strSomString is '123' the result of given Format
function will be '000123'.

For UPDATE query, type the Format function in the Update
row for the field you want to update. It is good idea to
put filed name in brackets , like Format
([MyFieldName],"000000")