Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Access 2000 and leading 0's
Author: Tangorre, Michael
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161769
----- 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
Author: Mark A. Kruger - CFG
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161766
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
_____
Author: Douglas.Knudsen
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161763
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
_____
Author: Greg Luce
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161713
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
_____
Author: Tangorre, Michael
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161712
----- 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
Author: Jeremy
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161711
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
_____
Author: Tangorre, Michael
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161709
> 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
Author: Philip Arnold
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161708
----- 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)
Author: Pascal Peters
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161707
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 -----
Author: Adkins, Randy
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161706
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
_____
Author: Katz, Dov B (IT)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161705
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
_____
Author: Jeremy
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32224#161704
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
