how do I force leading zeros (as in ZIPs) to be displayed in Acce.

R

Ralph

How do I force a leading zero in a ZIP code to be displayed in both the table
and form in ACCESS 2003? The table was imported from EXCEL where it was
defined as a ZIP under Special cell formatting.
 
F

fredg

How do I force a leading zero in a ZIP code to be displayed in both the table
and form in ACCESS 2003? The table was imported from EXCEL where it was
defined as a ZIP under Special cell formatting.
Click to expand...

If the Access field (in the table) is defined as a Number datatype, it
will not store leading zeros ... 02589 is the same number as 2589.

If the Access field is defined as a Text datatype it will store the
leading zeros..

Are they all 5 digit ZIPs?
You can either simply set the format property of the ZIP control (in
your report) to
00000
or explicitly format it within an expression:
=[City] & ", " & [State] & " " & Format([ZIP],"00000")

Or....

Change the field to a Text datatype and run an Update query to
permanently add the leading zero's.
Update YourField Set YourField.ZIP = String(5 - Len([ZIP]),"0") &
[ZIP];
 
Top