Add Leading Zeros

[Guest]

I want to add leading zeros to a column of text. They are unequal in length
and I want each record to have 9 place holders, i.e. 76, 309, 1245, 87356
convert to 000076, 000309, 001245, 087356.

Thanks for all solutions.

 

[Ken Snell [MVP]]

Run an update query:

UPDATE TableName
SET FieldName = Right("000000000" & [FieldName], 9);

 

[Rick B]

Did you try searching before posting your question? This is a very common
question. See the following...


http://groups-beta.google.com/groups?q=microsoft.public.access+leading+zeros


Rick B

 

[Van T. Dinh]

Are you sure you want 9 digits???

Your examples indicate you want 6 digits.

Adjust Ken's solution if required.

 

[Pieter Wijnen]

my version of Oracle LPAD

'assumes 1 char to PAD

Function LPAD(ByVal Inval as string, Optional ByVal TheLen As Long=10,
Optional ByVal PadChar as String=" ") as string

Dim Padstr as string
PadStr=String(Ord(PadChar),thelen)
LPAD=Left(PadStr,Len(PadStr-len(InVal)) & Inval)
end function

HTH

Pieter