Zip code format

[Joe K.]

Hi all, I am trying to create a mail merge. I have an
Excel file with all of the information in it. Name,
addr....etc. I live in an area where all of the zip codes
start with zero. In Excel 2002 I have the cells formatted
as a zip code and the zeros are present at the front as
they should be. I create a new blank database in Access
2002 and tell it to import from the Excel file.
Everything imports fine and goes where it should except
the zip code. All of the leading zeros are missing. I
cannot manipulate that field. I place a zero in the field
and tab to the next cell. The zero disappears. How can i
maintain the zip code format as to not lose those leading
zeros. Thanks in advance.

Joe K.

 

[Douglas J. Steele]

You've obviously got your zip codes stored as long integers, and numbers
don't maintain preceding zeros.

You can either switch your field type to text and update the zip codes to
have preceding zeros, or you can put a format on the field. My
recommendation would be for the first approach: unless you're going to do
arithmetic with the numbers, there's little reason to use a numeric field.

And actually, rather than changing the field type, I'd add a new text field,
populate it with the zip codes, then delete the old one. (Much safer that
way!)

To get the preceding zeroes in your update statement, you can either use
Format$([NumericZip], "00000"), or Right$("00000" & CStr([NumericZip]), 5)

 

[Joe K.]

-----Original Message-----
You've obviously got your zip codes stored as long integers, and numbers
don't maintain preceding zeros.

You can either switch your field type to text and update the zip codes to
have preceding zeros, or you can put a format on the field. My
recommendation would be for the first approach: unless you're going to do
arithmetic with the numbers, there's little reason to use a numeric field.

And actually, rather than changing the field type, I'd add a new text field,
populate it with the zip codes, then delete the old one. (Much safer that
way!)

To get the preceding zeroes in your update statement, you can either use
Format$([NumericZip], "00000"), or Right$("00000" & CStr ([NumericZip]), 5)

--
Doug Steele, Microsoft Access MVP

Hi all, I am trying to create a mail merge. I have an
Excel file with all of the information in it. Name,
addr....etc. I live in an area where all of the zip codes
start with zero. In Excel 2002 I have the cells formatted
as a zip code and the zeros are present at the front as
they should be. I create a new blank database in Access
2002 and tell it to import from the Excel file.
Everything imports fine and goes where it should except
the zip code. All of the leading zeros are missing. I
cannot manipulate that field. I place a zero in the field
and tab to the next cell. The zero disappears. How can i
maintain the zip code format as to not lose those leading
zeros. Thanks in advance.

Joe K.

.
Doug, thanks for the response. Forgive my ignorance.

I'm a network guy. When you say - To get the preceding
zeroes in your update statement, you can either use

Format$([NumericZip], "00000"), or Right$("00000" & CStr

([NumericZip]), 5) - where is this supposed to be placed?

Thanks again for your help.

 

[Douglas J. Steele]

When you create an UPDATE query, that's what you need to put as what to
update the field to.

If you're using the query builder, after you've selected the table and
dragged the fields to the grid, select Query from the menu, and pick Update
query. A new line will appear in the grid "Update To:" In the cell on the
Update To row underneath the text zip code field, put either of those
expressions I suggested. (Change NumericZip to whatever your numeric zip
code field is called)

--
Doug Steele, Microsoft Access MVP

Doug, thanks for the response. Forgive my ignorance.

I'm a network guy. When you say - To get the preceding
zeroes in your update statement, you can either use

Format$([NumericZip], "00000"), or Right$("00000" & CStr

([NumericZip]), 5) - where is this supposed to be placed?

Thanks again for your help.

 

[Joe K.]

-----Original Message-----
When you create an UPDATE query, that's what you need to put as what to
update the field to.

If you're using the query builder, after you've selected the table and
dragged the fields to the grid, select Query from the menu, and pick Update
query. A new line will appear in the grid "Update To:" In the cell on the
Update To row underneath the text zip code field, put either of those
expressions I suggested. (Change NumericZip to whatever your numeric zip
code field is called)

--
Doug Steele, Microsoft Access MVP

Doug, thanks for the response. Forgive my ignorance.

I'm a network guy. When you say - To get the preceding
zeroes in your update statement, you can either use

Format$([NumericZip], "00000"), or Right$("00000" &

CStr
([NumericZip]), 5) - where is this supposed to be placed?

Thanks again for your help.

.
Thanks very much.