Pad Text Field With Zeros in VBA

[jhrBanker]

I have several Text fields in a table. These fields contain both numbers and text (for example: 123, A532, BBB, Null, etc.). I need to concatenate the fields into a single string, with each field separated with a dash, and all fields being the same length (for example 6 positions), so that the result of the above fields would be 000123-00A532-000BBB-000000. The Format function works okay when there's only numbers in the field, but not when non-numeric characters are present (or null values).



Any suggestions?

[RuralGuy]

Why not just write your own User Defined Function (UDF)?

[jhrBanker]

Is that something you could help me with?

[RuralGuy]

Here's the Function that goes in a Standard Module:

Public Function PadIt(InString As String, Optional InLength As Integer = 6) As String
'-- Pad InString with leading zero's
   PadIt = String(InLength - Len(InString), "0") & InString
End Function

[jhrBanker]

Then to use it I would code as follows?
Call PadIt(Field1,"0")

Sorry, but I'm new at this.

[RuralGuy]

If you want to pad to 6 characters then you would use it like:
MyField = PadIt([Field1]) & "-" & PadIt([Field2])...
If other than 6 then:
MyField = PadIt([Field1],n) & "-" & PadIt([Field2],n)...
...where n is the length of the final padded string.

[jhrBanker]

Thanks for your help. I'll give it a try.

[RuralGuy]

Let us know how you make out.

[jhrBanker]

Works like a charm. Thanks for your help. Greatly appreciated.

[RuralGuy]

Excellent. Enjoy.