If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Text vs. number
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. |
#2
|
|||
|
|||
Text vs. number
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 wrote in message ... 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. |
#3
|
|||
|
|||
Text vs. number
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 wrote in message ... 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. |
#4
|
|||
|
|||
Text vs. number
Sco & Tina -
Thank you both for the insight! "tina" wrote in message ... 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 wrote in message ... 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. |
#5
|
|||
|
|||
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 wrote in message ... Sco & Tina - Thank you both for the insight! "tina" wrote in message ... 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 wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking Text Box | Richard | General Discussion | 8 | August 7th, 2004 04:47 AM |
convert text to number | Rob | Running & Setting Up Queries | 2 | June 25th, 2004 01:42 PM |
Vertical Text Orientation | Paul Anderson [MSFT] | Visio | 0 | May 11th, 2004 05:16 PM |
SUMIF mets text requirment a given number of cells to the left | Howard | Worksheet Functions | 1 | November 28th, 2003 10:58 AM |