Padding a field

[asexton]

I update a table using an update query. Part of the updating includes converting a field format from numeric to text. All of this works fine. However, the text field needs to contain 5 characters. For example if the data is 205 it needs to read 00205 or if the data is 1 it needs to read 00001. Can someone tell me how to pad the data with zeros using code until the field contains five characters?

Thanks,
Arlene

 

[SteveR77]

Here is the code you will need to put in a Module.

Public Function Padding(TheValue As Variant)



Select Case Len(TheValue) ' Evaluate Number.
Case 4
Padding = "0" & CStr(TheValue)
Case 3
Padding = "00" & CStr(TheValue)
Case 2
Padding = "000" & CStr(TheValue)
Case 1
Padding = "0000" & CStr(TheValue)


End Select



End Function


In the query you would need to have the following -

PaddedField: Padding([FieldToBeZeroPadded])


Hope this helps,

Steve

 

[asexton]

Thanks, I was trying to do a loop, but kept getting an error message. Your suggestion is a much simpler way.

 

[CosmoKramer]

Or you could simply add a user-defined field to your query. Something like:

Zeropadded: Format([YourField],"00000")

Hoc nomen meum verum non est.

 

[SteveR77]

The looping part is handled by the query for you.

The part I left out is at the end of the case statement a

Case Else statement can be added to trap for anything unexpected.