Field Type

Hey,

I have a field full of numbers, however the format is text, not number. I need to change the format to number to run queries that return the sum, average, etc etc of those numbers, but as far as I know I can't do that without making the format "number." If I try to change the format to number, Access tells me it's going to delete 19,000 records, which I really don't want it to do. Is there a simple way to change the format of text to number or can I run those equations of Sum, Average, etc etc using the text format?

On Mon, 7 Jun 2004 09:11:04 -0700, "Matt"
wrote:

Hey,

I have a field full of numbers, however the format is text, not number. I need to change the format to number to run queries that return the sum, average, etc etc of those numbers, but as far as I know I can't do that without making the format "number." If I try to change the format to number, Access tells me it's going to delete 19,000 records, which I really don't want it to do. Is there a simple way to change the format of text to number or can I run those equations of Sum, Average, etc etc using the text format?

The "FORMAT" of a field and the "DATATYPE" of a field are two
different properties! A Text field can contain any ASCII character
(including numeric characters); a Number field doesn't contain
characters at all, just binary bits. Changing one to the other
requires a calculation to turn a text string "127" to the binary
string 000000000000000001111111.

I would suggest either of two alternatives:

- If this field really is a number field to be used routinely for
calculations, store it as a number field. To do so efficiently create
a new table by copying the structure only of this table to a new
table, change the datatype of the field from Text to either Number -
Long Integer (if you don't need decimal places), Currency (if you need
up to four decimals and don't want roundoff error) or Number - Double
(if you need more than four decimals). Then run an Append query to
migrate the data into it.

- If the field sometimes contains text values (e.g. "Not Applicable")
use the Val() function to convert it to a numeric value, and Sum or
Average that calculated field.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public