How to add a 00000 zip code?

You have two options:

1.  With a column (field) of number data type, format the column as 00000.  This will display the zip code as five digits with leading zeros where necessary, but the actual value will be numeric.

2.  With a column of short text data type, the value will be whatever is entered, including any leading zeros.  If you change the data type of  the column from number to short text, you can then add leading zeros to all existing values where necessary by executing the following UPDATE query:

UPDATE [NameOfTableGoesHere]

SET [NameOfZipCodeColumnGoesHere] = FORMAT([NameOfZipCodeColumnGoesHere], "00000");

The value will now be a string expression which includes the leading zeros.

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.

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.

To add to Peter's reply, after you have changed the field's Format property and saved the amended table, any new forms, reports or queries which you create should inherit the property and show the data formatted with leading zeros.  However, any which you created before changing the property will not.  In those you will need to change the Format property of the controls in forms or reports, or of the column in a query, which you would do in their properties sheets in design view.

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.