Add leading zero if field has only 1 digit

[Guest]

I've tried using a lot of the suggestions posted here to add a zero to a
field if it contains only one digit. If it contains 9 it should be 09, if it
contains 25 its fine.

I know I'm close, but missing something. I've tried:

UPDATE PSoftDownload
SET Sect = "0" & Sect
WHERE Left(Sect,1) = "";

Thanks for your help -

 

[Rick B]

Is the field a NUMBER type or a TEXT type in your table? If it is a number,
then you can't update the table to include the zero. You can only modify
the DISPLAY format of your "number" in reports, forms, and queries. Your
code below indicates you are trying to change the data in the table. In
order for that to work, you'd have to be working with a text field.

Not sure what your code is doing. Personally, I'd write code to say "if the
length of the field is "1" then update the field to equal
"0"&[currentfieldvalue]. In other words, concatenate a "0" to the left of
the current value if the field is only one digit in length.

 

[Guest]

if it is always a 2 character number then try
SET Sect = Right("0" & Sect,2)

 

[Ken Snell [MVP]]

Try this:

UPDATE PSoftDownload
SET Sect = Right("00" & [Sect], 2);

 

[ja]

This is my suggestion:

UPDATE PSoftDownload
set Sect = 0 & Sect
WHERE (((PSoftDownload.Sect) Like "?"));