How do I replace leading zeros in my zipcodes?

[tvsmvp]

I have received a database loaded with addresses - with the zipcodes formatted as numbers (which, I believe is incorrect). Naturally, when I try to print them out zips from the east coast (the ones using a leading zero) appear with four digits.

How do I put back the leading zeros in access (making all zips five digits again)? It seems like there's a pretty simple way - but I don't want to reinvent the wheel fixing this. Thoughts?

 

[TheAceMan1]

How are ya tvsmvp . . .

So you've realized that a zipcode [blue]should be text![/blue] . . . I hope!

What you need to do is:
[ol][li]Add a zipcode field to the approriate table as [blue]Text[/blue] data type.[/li]
[li]Run an [blue]update[/blue] query to make the conversion![/li]
[li]The basic conversion function would be:

[blue]Public Function FixZip(Zip As Long) As String
   FixZip = String(5 - Len(CStr(Zip)), "0") & Zip
End Function[/blue]

[/li][p/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

Sorry about the mess!

How are ya tvsmvp . . .

So you've realized that a zipcode should be text! . . . I hope!

What you need to do is:
[ol][li]Add a [blue]zipcode field[/blue] to the approriate table [blue]as Text[/blue] data type.[/li]
[li][blue]Run an update query thru a function to make the conversion![/blue]
The basic conversion function would be:

[blue]Public Function FixZip(Zip As Long) As String
   FixZip = String(5 - Len(CStr(Zip)), "0") & Zip
End Function[/blue]

[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[tvsmvp]

Right - I've found enough threads and articles saying that zips should be text to figure that was probably the case. But I'm still new enough that I could be convinced of nearly anything. The table in question came to me (formatted that) way from a large company that, frankly, should know better. But, oh well, it gave me a chance to learn something new.

I've been in a desparate bid to get these mailings out tomorrow - so I continued combing through help pages till I ran across a way to "get me through the night." And that's to add "1" plus a bunch of zeros to each zip record, then use the Right function to trim it back to five correct digits (as so: ZipCodeCorrected: Right([ManchesterNH150].[ZIP]+1000000,5)).
Now, of course, that still leaves me with the data formatted as numbers. Which is where you come in, AceMan. Thanks much for the widget.

 

[TheAceMan1]

tvsmvp . . .

So . . . are you closing the matter?

See Ya! . . . . . .

 

[tvsmvp]

Yep - between your solution and my (found) semi-solution, I think we got it cornered. Is there a way to mark a question "solved" on this forum?

Thanks again for your help

 

[TheAceMan1]

tvsmvp . . .

You/ve already done so! . . .

[blue]Cheers from Tek-Tips![/blue]

See Ya! . . . . . .