How to add a 00000 zip code?

More details might be helpful here.

I am left to assume you mean you have a zip code field in a table in an Access relational database application.

I further assume that means you have defined this zip code field as a string, not a number (which is the right way to do it).

I further assume that what you need to do is pad out any zip codes with fewer than 5 digits with leading 0's so get to the correct length.

If that is all correct, then the next question is where and how this needs to be done. Is it a one-time fix up for existing values? Is it something you want to do when adding new zip codes in bulk from another source, such as a text file? Or is it something that needs to happen when you enter a zip code through a form? Or some other operation I'm not thinking off at the moment?

Thanks for providing enough information to attempt a response.

In general, though, you can left pad values like this:  Right("0000" & [InvalidZIPCodeField]), 5) would do it, but how and where you do it is yet to be determined.

Hi...thank you for getting back to me.

I still don't understand what to do. I can't find a 'format' option for a field in Access like you can for a cell/s in Excel. I have copy and pasted close to a thousand contacts into Access database from an Excel spreadsheet and the zip codes that have a '0' in front of them didn't copy over. When this has happened in Excel in the past, I simply right-clicked and selected 'Format cells', then 'special', then 'zip code' and all of the 0's added on to the front of the zip codes.

How do you do the same in Access for a field or column of zip codes?

Please explain step by step.

Thank you.

Open your table in design view. Two panes will open. The top pane lists the fields in the table. The bottom pane lists the properties of the selected field.

Select the field in question (the zip field).

Follow Ken’s instructions.

Is that a good idea?

Assuming we are talking about the US, the 00000 zip code does not exist. So why enter it in the database. Just send that postcard to "One Microsoft Way, Redmond, WA" without a zip code and it will get there just fine.

Entering "magic values" like this may come to haunt you. If the value is not known, leave the field blank.

There are apparently some zip codes in the US with 1 or 2 leading zeros. *If* you are storing Zipcode as a number, you can use the Format property to "add" the leading zeros, as others have described. Personally I think it's better to store it in a Text(5) field and require 5 digits to be entered.

thank you for your response.  Still alot of info for me to take in where usually a step by step explanation helps me the most.  Here's what a friend via Facebook explained that made the difference (along with a pic of what she was talking about with red arrows that pointed exactly where to go)...

Open the table in Design view.
Make sure the Datatype of the field is set to Number.
Select the General tab.
Click on the Format line, and set the format to 00000.

Thanks again for all who responded. Have a wonderful Thanksgiving celebration and...be safe.

Is that a good idea?

Assuming we are talking about the US, the 00000 zip code does not exist. So why enter it in the database.

Tom, does the OP actually want to do that?  Despite the title of the thread, I think the OP just wants to format the values as five digits, with leading zeros where necessary, not include a value of 00000.

On the basis of a couple of tables I received a couple of years ago from a bunch of nuns (I'm not making this up) there are 194 zip codes in the US with two leading zeros,  mapping to 3,087 named locations, and 3,475 with one leading zero, mapping to 6,778 named locations.

I took the request the same way Tom did, with the exception that I anticipated the five zeros to be a pattern, not a specific value. In my opinion, such strings should be handled as strings, even when composed entirely of digits, not alpha characters. And, since the standard is five characters, or digits, they should be stored that way. I understand UK Postal Codes are unambiguous in that they consist of a mixture of alphas and digits, but we have to deal with it in the US. 

Actually, in composing my initial response, I started out saying, "More details might be helpful here."

As I see it, the danger in trying to address vague questions is that more than one interpretation is possible, leading to alternate approaches. 

Hence, I repeat.

More details -- from the OP -- might be helpful here.