Access 2000 and leading 0's

Hello I have a field in access 2000 holding zip codes, and new england zips start with 0. I know it is a db thing as when i type in the zip directly in the db it cuts off the zero. The field type I am using now is long integer. What could be causign this? Should I use something other than long int. Is there a default in access? Thanks alot Jeremy

----- Excess quoted text cut - see Original Post for more ----- Any kind of Int field will always trim off the leading zeros, because it stored it as the number itself, so 00001 will always be 1 Use some kind of text based field Especially if people use the "long" version of their zip (with the extra digits after a hyphen)

I'd use char or varchar... a 0 at the beginning of an integer has no impact on the value stored, and is thus ignored by the db. -dov   _____ Sent: Thursday, April 29, 2004 9:30 AM To: CF-Talk Subject: Access 2000 and leading 0's Hello I have a field in access 2000 holding zip codes, and new england zips start with 0. I know it is a db thing as when i type in the zip directly in the db it cuts off the zero. The field type I am using now is long integer. What could be causign this? Should I use something other than long int. Is there a default in access? Thanks alot Jeremy   _____  

Since you can have leading zeros, I would use a varchar field Hello I have a field in access 2000 holding zip codes, and new england zips start with 0. I know it is a db thing as when i type in the zip directly in the db it cuts off the zero. The field type I am using now is long integer. What could be causign this? Should I use something other than long int. Is there a default in access? Thanks alot Jeremy   _____  

Always use (var)chars for zip code. Some countries even have non-numerical characters in them. Pascal ----- Excess quoted text cut - see Original Post for more -----

> Hello I have a field in access 2000 holding zip codes, and > new england zips start with 0. I know it is a db thing as > when i type in the zip directly in the db it cuts off the > zero. The field type I am using now is long integer. What > could be causign this? Should I use something other than long > int. Is there a default in access? Thanks a lot In Access 2003, I have a zipcode field as well with the following attributes: Data Type: Number Field Size: Long Integer Format: 00000 That allows me to have zipcodes beginning with 0, as found in the US (New Hampshire) and Puerto Rico, etc.. HTH, Mike

Well that's interesting I wil keep that in mind next time. I just got done changing them all to text feilds. thanks Jeremy > Hello I have a field in access 2000 holding zip codes, and > new england zips start with 0. I know it is a db thing as > when i type in the zip directly in the db it cuts off the > zero. The field type I am using now is long integer. What > could be causign this? Should I use something other than long > int. Is there a default in access? Thanks a lot In Access 2003, I have a zipcode field as well with the following attributes: Data Type: Number Field Size: Long Integer Format: 00000 That allows me to have zipcodes beginning with 0, as found in the US (New Hampshire) and Puerto Rico, etc.. HTH, Mike   _____  

Jeremy,   Depends on your business rules. Canada has alpha chars in their postal codes. Most of my experience has been with applications that have to work with other users outside of just US users. I think the only thing to be gained by using numeric is the ability to do math on the column. Greg Well that's interesting I wil keep that in mind next time. I just got done changing them all to text feilds. thanks Jeremy > Hello I have a field in access 2000 holding zip codes, and > new england zips start with 0. I know it is a db thing as > when i type in the zip directly in the db it cuts off the > zero. The field type I am using now is long integer. What > could be causign this? Should I use something other than long > int. Is there a default in access? Thanks a lot In Access 2003, I have a zipcode field as well with the following attributes: Data Type: Number Field Size: Long Integer Format: 00000 That allows me to have zipcodes beginning with 0, as found in the US (New Hampshire) and Puerto Rico, etc.. HTH, Mike   _____  

----- Excess quoted text cut - see Original Post for more ----- You are better off using text anyways, since some foreign "zipcodes" contain non alphanumeric characters. Good luck. Mike

try something like this.... =text(foo.goo, "00000")  Id you are using HTML tables to generate your xls file, something like this then  <td>=text(#foo.goo#, "00000")</td> Doug Hello I have a field in access 2000 holding zip codes, and new england zips start with 0. I know it is a db thing as when i type in the zip directly in the db it cuts off the zero. The field type I am using now is long integer. What could be causign this? Should I use something other than long int. Is there a default in access? Thanks alot Jeremy   _____  

Yes - you should always use a character field for zips. there is no reason to use math functions on them - so char is the way to go. Plus, different countries and areas have different conventions. For example, in some parts of the country they use a dash for zip + box, as in  85838-3003.  If f you tried to store this as an int would subtract the two and store the total - ending up with 82835 - and your mail would end up in North Dakota.  This would confuse the post man because no one actually lives in North Dakota (although there have been rumors). -Mark   try something like this.... =text(foo.goo, "00000")  Id you are using HTML tables to generate your xls file, something like this then  <td>=text(#foo.goo#, "00000")</td>   Doug   Hello I have a field in access 2000 holding zip codes, and new england   zips start with 0. I know it is a db thing as when i type in the zip   directly in the db it cuts off the zero. The field type I am using now   is long integer. What could be causign this? Should I use something   other than long int. Is there a default in access? Thanks alot   Jeremy     _____

----- Excess quoted text cut - see Original Post for more ----- In addition, you always calculate distances and radius from zip and such using latitutde and longitude, so again char fields should be used. I was merely showing that in Access you could store a number with leading zeros. Mike