Add Leading 0 to a Zipcode

[tylerpickering]

Hi guys--



I have some data going into my access database from excel that contains zip codes--my issue is that for states with a leading zero, this sometimes gets cut off.

Ex. zip code 1111 needs to be 01111.

Is there a way to check the length of the text and add a leading zero if it's only 4? I know in Excel it would be something like =if(len(A1)<5,0&A1,A1), but I'm pretty new to access and I'm not sure where to start with this.

Thanks in advance!

[pbaldy]

I would have zip codes in a text field, but try

Format(FieldName, "00000")

[tylerpickering]

It is being pulled to access as a text field, the problem is in excel; it's being sent to me without the zeroes already

[pbaldy]

So did the function work for you?

[tylerpickering]

Said it failed because a type conversion failure--is this because my zip code field is set to text?

[pbaldy]

No, just tested on a text field with numbers in it.

[tylerpickering]

Hmm I'll keep trying

[tylerpickering]

Ah got it to work, thanks for your help!

[pbaldy]

Happy to help!