Change values to 2-digit.

[Julian Milano]

I have a field which is TEXT. It has numbers in it, currently. The numbers
are single digit ("1", "4", "9", etc) and double-digit ("10", "21", "33",
etc). I want to convert all single DIGITS to double digits by adding a zero
at the front, so "1" becomes "01" and "5" becomes "05".

What's the SQL for this?

 

[Van T. Dinh]

UPDATE YourTable
SET YourField = Format(CInt([YourField]) , "00")
WHERE Len([YourField]) = 1

HTH
Van T. Dinh
MVP (Access)

 

[Steve Schapel]

Julian,

UPDATE YourTable SET YourField = "0" & YourField
WHERE Len(YourField)=1

Another approach would be to leave the data as it is, and then use a
Format function, i.e. Format([YourField],"00") in a query, or the
Format property of a textbox, whenever you need to use this data for
your purposes on form or report.

- Steve Schapel, Microsoft Access MVP