Text vs. number

[Guest]

I was under the impression that a field should always be formatted as text
if the contents were not going to be used in calculations, so I formatted
fields such as Social Security numbers and employee numbers as text. But I
notice many examples in the NGs using numbers for this type of data.

Should I change the formatting? Does it matter? One issue that comes to
mind with formatting as text is that someone could enter a letter into the
SSN field. Any other reasons?

Thank you for any suggestions.

 

[M.L. Sco Scofield]

anonymous,

You are right. If it's not an amount or a quantity and it is not used in a
calculation, it should be text.

General rule, if we call it a number, it is most likely a code and *not* a
number.

Things like phone numbers, social security numbers, inventory numbers,
employee numbers, etc. are not numbers. They are codes because you don't do
any calculations with them.

Things like cost, price, quantity, amount, etc., are numbers. We frequently
multiply, add, and subtract these kinds of things. And they typically do
*not* have the word "number" in them.

One big problem people have when using the wrong type is leading zeros.
Although numbers can be formatted with leading zeros, as in algebra, they
have no meaning. I.e., in algebra, 1, 01,001, etc. are all identical values.
Frequently, with codes, leading zeros can be significant. As inventory
numbers, 1, 01, 001 could all be distinctly different items.

If you are seeing examples of using numeric fields for things like phone
numbers and zip codes, I assure you they are from beginners *asking*
questions and not from any of the "experts" *answering* questions.

This is database design 101.

Hope this helps.

For more detailed information on database design, I recommend getting a copy
of "Database Design for Mere Mortal" by Michael Hernandez.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[tina]

well, first, text vs number is talking about *data types*, not formatting.
formatting only affects the way you see a value (text or numeric), it does
not affect the value itself or how the system processes it.
the general rule that the basic textbooks or classes usually teach is,
indeed, "numeric for a field that you're going to do math on, otherwise use
a text field". personally, i usually amend that rule to include
1) numeric data type for a field you're going to sort on, because a numeric
field will sort as '1 2 3 4 5 6 7 8 9 10 11 12...' while a text field will
sort as '1 10 11 12 2 3 4 5 6 7 8 9...'.
2) if the value of the field will always be between 0 and 255 in whole
numbers, because you set the Field Size as Byte, and save some space.

hopefully some more people will post replies, and we'll both benefit from
additional input on the subject.

hth

 

[Guest]

Sco & Tina -

Thank you both for the insight!

well, first, text vs number is talking about *data types*, not formatting.
formatting only affects the way you see a value (text or numeric), it does
not affect the value itself or how the system processes it.
the general rule that the basic textbooks or classes usually teach is,
indeed, "numeric for a field that you're going to do math on, otherwise use
a text field". personally, i usually amend that rule to include
1) numeric data type for a field you're going to sort on, because a numeric
field will sort as '1 2 3 4 5 6 7 8 9 10 11 12...' while a text field will
sort as '1 10 11 12 2 3 4 5 6 7 8 9...'.
2) if the value of the field will always be between 0 and 255 in whole
numbers, because you set the Field Size as Byte, and save some space.

hopefully some more people will post replies, and we'll both benefit from
additional input on the subject.

hth


But

 

[M.L. Sco Scofield]

You're welcome.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com