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
|
|||
|
|||
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? |
#2
|
|||
|
|||
Field Type
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 |
Thread Tools | |
Display Modes | |
|
|