A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Text vs. number



 
 
Thread Tools Display Modes
  #1  
Old August 13th, 2004, 02:04 AM
external usenet poster
 
Posts: n/a
Default 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  
Old August 13th, 2004, 02:36 AM
M.L. Sco Scofield
external usenet poster
 
Posts: n/a
Default 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  
Old August 13th, 2004, 02:40 AM
tina
external usenet poster
 
Posts: n/a
Default 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  
Old August 13th, 2004, 04:35 PM
external usenet poster
 
Posts: n/a
Default 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  
Old August 14th, 2004, 04:20 PM
M.L. Sco Scofield
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 11:58 AM


All times are GMT +1. The time now is 02:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.