add a trailing zero when four digits.

[subs]

hi

i want to add a trailing zero to every data in a column whenever the
data has only four digits. For example
when data is 7893 , i want to make it 07893 and so on. What will
be the update query and the criterion?

 

[Tom van Stiphout]

On Mon, 11 May 2009 21:53:04 -0700 (PDT), subs <[email protected]>
wrote:

Something like:
update myTable
set myField = Format(myField, "00000")
where Len(myField) = 4

-Tom.
Microsoft Access MVP

 

[Paul Doree]

or, if you're using an update query the 'update to' part should read like:

iif(len(myfield) = 4, "0" & [myfield], [myfield])

 

[John W. Vinson]

hi

i want to add a trailing zero to every data in a column whenever the
data has only four digits. For example
when data is 7893 , i want to make it 07893 and so on. What will
be the update query and the criterion?

That looks like a *leading* zero not a trailing one.

One important question: is this field Text or Number? If it's Number, then be
aware that the value is stored as a binary, and that 7893 and 07893 and
0000000007893 are all *exactly the same indistinguishable value* in your
table. You can set the Format property of the field to

"00000"

to always show five digits, but the storage is the same.

If the field is Text (which it should be for identifiers like postcodes or
part numbers), you can use an update query updating the field to

Right("00000" & [fieldname], 5)

to update "7893" to "07893" or "7" to "00007".