I NEED A '0' (ZERO) IN FRONT OF THE ZIP CODE

[Zetony]

sI'm writing an Access database program. The field in my table that stores
the Applicant Zip Code automatically removes the Zeros that are in front of
the numbers, thereby changing the code format. In New Jersey State all Zip
Codes starts with a Zero in front.

And I don't want to change the field from numeric to text or any other thing
so that the users of the program will not input wrong infor to the table.

So can someone, please help me by telling me what I can do to retain the
zeros in front of those numbers, so that my program will not continue to be
messed up .

Thanks.

 

[KARL DEWEY]

You can make the display show the zeros.
Format([YourZipField],"00000")

I think it would be better to change to text field and use input mask to
eliminate input errors.

 

[fredg]

sI'm writing an Access database program. The field in my table that stores
the Applicant Zip Code automatically removes the Zeros that are in front of
the numbers, thereby changing the code format. In New Jersey State all Zip
Codes starts with a Zero in front.

And I don't want to change the field from numeric to text or any other thing
so that the users of the program will not input wrong infor to the table.

So can someone, please help me by telling me what I can do to retain the
zeros in front of those numbers, so that my program will not continue to be
messed up .

Thanks.

The Zip code field SHOULD be a text datatype field. You are never
going to do math using Zip codes.
Text datatype retains leading zeros.
Number datatype does not.

Unless you wish to change the field datatype, the best you can look
forward to is always using an expression, such as:
=Format([ZIP],"00000")
wherever you need to show the Zip code in 5 characters, and adjust the
format if you need 00000-0000.

 

[Ofer]

You can create a query based on the table that will format the field with
leading zero's

Select Format(FieldName,"000000") As NewZipCode From TableName

Change the amount of zero's to the field length of the zip code


Or
Select "0" & FieldName As NewZipCode From TableName
=======================================
You will be able to display the field in the query but not to update it.

 

[John Vinson]

sI'm writing an Access database program. The field in my table that stores
the Applicant Zip Code automatically removes the Zeros that are in front of
the numbers, thereby changing the code format. In New Jersey State all Zip
Codes starts with a Zero in front.

And I don't want to change the field from numeric to text or any other thing
so that the users of the program will not input wrong infor to the table.

So can someone, please help me by telling me what I can do to retain the
zeros in front of those numbers, so that my program will not continue to be
messed up .

Thanks.

Do use Text, as suggested. If you give the field an Input Mask of

00000

the users will be prevented from entering any nonnumeric characters.

John W. Vinson[MVP]

 

[Mike Labosh]

ZIP codes / Postal Codes, phone numbers, social security numbers [the
pattern here is any numerical information that requires leading zeros,
formatting etc] should ALWAYS be stored as a "Text" type field.

Change the data type of your column from Number to Text, set an appropriate
length, and then run this query:

UPDATE YourTable
SET YourZipCodeField = "00000" + RIGHT(YourZipCodeField, 5)

That will, for example, change "123" to "00123" in your data.

If your ZIP codes are stored as ZIP+4 (19021-6020), you will have to modify
the expression.
--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

 

[Zetony]

Do use Text, as suggested. If you give the field an Input Mask of

00000

the users will be prevented from entering any nonnumeric characters.

John W. Vinson[MVP]

 

[Zetony]

I thank You all for your contribution. That is great. Keep it up. I
appreciate.

Happy New year to all in this great community.


Thanks
Zetony.