format cells in Access

H

hadams0617

Can cells in Access be formatting similar to those in Excel, i.e. changing
zip codes to have a zip code format? The problem i have is that the database
i want to work with involves zip codes from the northeastern states that have
zip codes that start with 0. Access is eliminating these and only reads a
four dgit zip code.

Thank you
 
J

Jeff C

In the design mode for the field the format type should be text..and then use
the input mask for zip codes
 
C

ChrisJ

Look at your table in design mode, put your cursor in the zip code field,
under the properties, go into the "Input Mask" and specify that you want it
to look like a zip code.
 
F

fredg

Can cells in Access be formatting similar to those in Excel, i.e. changing
zip codes to have a zip code format? The problem i have is that the database
i want to work with involves zip codes from the northeastern states that have
zip codes that start with 0. Access is eliminating these and only reads a
four dgit zip code.

Thank you
Click to expand...

That's because you are incorrectly storing the zip code in a Number
datatype field. To correctly display the leading zeros in a number
datatype field, you can set the format property of the control to:
00000
if all the zip codes are only 5 characters.

I don't think you are ever going to perform mathematical operations on
a zip code. Make the field a Text datatype and future entries will
retain the leading zeros.
After changing the field definition to Text, run an Update query to
change current data:

Update TableName Set TableName.[ZipField]= Format([ZipField],"00000");
 
M

Mike Labosh

That's because you are incorrectly storing the zip code in a Number
datatype field. To correctly display the leading zeros in a number
datatype field, you can set the format property of the control to:
00000
if all the zip codes are only 5 characters.

I don't think you are ever going to perform mathematical operations on
a zip code. Make the field a Text datatype and future entries will
retain the leading zeros.
After changing the field definition to Text, run an Update query to
change current data:

Update TableName Set TableName.[ZipField]= Format([ZipField],"00000");
Click to expand...

Even more importantly, Access has a mis-feature wherein, if you type a
nine-digit zip code into a numeric field:

12345-1234

Access treats the hyphen as a subtraction operator, performs the math behind
your back, and only stores the result:

11111

Back in 1993, I spent many hours gritting my teeth over this.
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
 
T

Tony Toews

fredg said:
I don't think you are ever going to perform mathematical operations on
a zip code. Make the field a Text datatype and future entries will
retain the leading zeros.
Click to expand...

Agreed. And Canada, the UK and I'm sure other countries use
alphanumerics in their postal codes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Top