[AccessD] Re: Number vs text data type

Gustav,

See responses below:


At 10:24 AM 10/14/2003 -0500, you wrote:



>With all respect I disagree. The rule should be reversed:

RLS:  Isn't this list great, we can disagree and stay friends.  8-) (Not 
trying to be funny)


>   If you are not going to use the number as a string,
>   store it as a number.
>
>The exceptions are only those where the numbers exceed the capacity of
>your database engine, normally at least 14 digits (Jet: Currency) and
>a Long Integer is always available which holds 9 digits.

RLS:  By using scientific notation, you can go past this also.
Decimal = 10 ^28 -1


>Also, as several have stated, there may be some good reason for not
>storing a number as a number but as a numeric string. The most common
>situations are those where the place holder has a meaning. An example
>is old accounting systems where longer account numbers indicate lower
>levels relative to shorter account numbers:
>
>        4        Main account
>        48       Sub account of 4
>        482      Sub account of 48
>        8        Main account
>        ...      etc.

RLS:  True and in this case, it is "sorting" the way you really want it to.


>One way to avoid strings in this case is to decide on a maximum length
>for account numbers, say 6, and append traling zeroes:
>
>        400000   Main account
>        480000   Sub account of 4
>        482000   Sub account of 48
>        800000   Main account
>        ...      etc.
>
>Another example is something like product numbers where leading zeroes
>are used but the length of the numbers varies. Such a combination
>cannot be sorted by formatting with leading zeroes as you don't know
>how many zeroes to add:
>
>        00459208
>        69392110
>        0002918
>        33981
>        88470098
>        0877281

RLS:  True, but just because we say "product number" does not mean that it 
really is a number.  Product Identifier would really be more accurate.  I 
would never think of making this a number and from what you have said, 
neither would you.


>However, in many countries postal codes are pure numerics and of fixed
>length, so there's no reason to store a, say, four digit number as a
>string when a two-byte Integer can do.

RLS:  Sure there is.  Like the US zip codes, they can start with a zero.


>And here every company is identified for the authorities by a fixed
>8-digit number like 12002696. Although it should be formatted as
>12-00-26-96 I see no special reason for storing it as text.

RLS:  As long as the first character can not be a zero, you are okay.  Just 
like in the US, our Social Security Number is a format of
999-99-9999, I would never store it as a number because some of them start 
with zero.

Just call me "Old School" 8-)))

Robert