Difference between Numeric and text

[amerifax]

I have a field that will only get numeric values, numbers. And it will always have four characters or less. I do have times when I worry my data by over eighty's certain size or I average value, or both. Is this possible when using a text field.

So I'm looking for advice whether I should text or numeric.
Bob

 

[Alansidman]

I do have times when I worry my data by over eighty's certain size or I average value, or both.

What does this mean? I don't understand.

I usually make fields text (even if numerical looking) unless they require calculations to be performed on them. If that is the case, then I format the fields as numerical or dates as the case may require.

 

[amerifax]

At times I trust Dragon more than I should. Case in point.
There are times I query by size and average or both. Wow. I see what you mean.
Bob

PS you didn't answer my question. If I'm going to calculate I'm going to need something other than text.

 

[Galaxiom]

There are good reasons why you shouldn't use text fields to hold numbers.

Whole numbers less than 32,768 can be stored as Long Integer. It requires two bytes for each record. Two bytes are required for each character in a text field.

Indexing is slower on a text field.

I usually make fields text (even if numerical looking) unless they require calculations to be performed on them.

I have often heard that said but none of its proponents has ever provided an explanation.

 

[nanscombe]

One problem with using text fields to hold numeric data is how do you deal with values like "< 10", "25%", "fourteen", "Incomplete count" or even data entered in the wrong field like "Fred Smith".

If you need to produce meaningful reports from the data you would have to be very strict about the text people could enter.

 

[gemma-the-husky]

What does this mean? I don't understand.

I usually make fields text (even if numerical looking) unless they require calculations to be performed on them. If that is the case, then I format the fields as numerical or dates as the case may require.

if you mean phone numbers etc, then I can understand this. if you genuinely mean you are storing numeric data as text strings, then I think this is a bad idea.

 

[amerifax]

I have a very clear understanding of the issues surrounding text and numeric fields. thanks to all the above.
Bob

 

[Alansidman]

I am referring to things such as values that really will never require or be required to be calculated. Perhaps an invoice number. It is not often and really depends on how the data will be employed. Bills of Lading, part numbers.

 

[Galaxiom]

I am referring to things such as values that really will never require or be required to be calculated. Perhaps an invoice number. It is not often and really depends on how the data will be employed. Bills of Lading, part numbers.

Invoice numbers are usually incremented so that requires maths.


We have already pointed out some disadvantages of using text to store numbers. Could you please indicate what advantage there would be to using text for numbers.

 

[Alansidman]

I should have added earlier, that this generally happens when importing spreadsheet data for manipulation and then exporting back out to Excel. Further, it generally happens when importing data that requires preservation of preceding zeros to maintain continuity. Again, in every case that I have used this technique, there is not a requirement for calculations.

I do not dispute your arguments, I was only indicating where I had done the opposite when the values held no numerical meaning other than identification.

 

[Galaxiom]

I should have added earlier, that this generally happens when importing spreadsheet data ...

It is definitely a significant omission that changes the basis for the advice entirely. Most experienced developers will use text for importing a spreadsheet or text file to ensure there are no import errors. Indeed this would be case regardless of whether calculations are ultimately performed or not.

It is a staging table and the text is typically converted as appropriate while the data is written to the permanent tables. If it is a number I would still ultimately store it as a number field

What I wouldn't do is store a numbers as text just because it isn't used in arithmentic. This is the reason you continue to quote as a justification for using text to store a number. It is irrelevant.

I was only indicating where I had done the opposite when the values held no numerical meaning other than identification.

You have belatedly cited a special case of an import table but still not shown any reason why not usng arithmetic constitutes a good reason to store numbers as text.

You cannot provide a reason because there is no reason. You have now resorted to ofuscating the issue with discussion of Excel imports.

Storing numbers not used in arithmetic as text is another of those frequently repeated Access myths adopted by those who don't question why somethng should be done in a certain way.

It is important to understand why you do things in a certain way before offering your practices as advice.

 

[amerifax]

I do understand the concept. Any numbers and text are basically invoice numbers in that type of thing. Any numeric value that will not require a calculation.
Bob

 

[Galaxiom]

I do understand the concept. Any numbers and text are basically invoice numbers in that type of thing. Any numeric value that will not require a calculation.
Bob

See what happens Alan? Now Bob is taking on your advice and will probably go on repeating it to others.

This is how myths are propogated.

 

[Galaxiom]

The only reason I can think of to store numeric data as text would be to if it had significant leading zeros.

For example 00123, 0123, and 123 all being separate records.

However such a practice would be very ill advised.

 

[Alansidman]

Galaxiom,
I have been chastised and am repentant on this issue. I will no longer attempt to format numbers as text except in dire circumstances. Perhaps only life or death. Please forgive me.

Alan

 

[Pat Hartman]

Any "numeric" field that might require a leading zero must be stored as text. For those of us who live in New England, we are quite aware of this issue since our zip codes start with "06" and Access will strip the leading zero and leave us with an invalid code if zip is stored as a number. I will extend this to other code fields that might have been imported from legacy databases.

You need to be extremely careful if you decide to store variable length "numeric" fields as text since they won't sort the way you expect. For example, the text value 10 will sort ahead of the text value 2 and 10 will be considered to be less than 2 if you compare the two.

 

[Cronk]

In addition to postal zip codes, phone numbers with distance or international prefixes need to be stored in text fields to preserve leading zeros. Also in monetary checks.

Depending on the data in the situation being addressed, digits need to be stored in a text field. (I deliberately call them digits because they are not numeric data that have arithmetic operations performed on them, like why would you want to total several phone numbers?)