How to convert text fields to numeric

[rehanemis]

Hi,

I have an ms access Database(2013 version). There are about 10000 records. There are some columns with field property of "short text" but contains the values like that 0.4,7, 9.0 etc I would like to convert the "short text" into "double" without loosing information.

Can i do that using any query?

Looking for your good answer.

Thanks

 

[Vonik]

I'm still a n00b so dont trust me but i think you can't store a number like 9.0 as a number data type -- it will drop the .0 after the 9. To store the number as "9.0" i think you need to use short text.

 

[plog]

Vonik is right and wrong. Numerically 9.0 is equivalent to 9 and you can store that as a number. If you believe the .0 is signficant, then you aren't (and shouldn't be) using a number field.


In a query all you have to do to convert a field's values to numeric is do a mathematical operation on them. So my suggestions is to multiply that field by 1 to convert your values to numbers:

NumericValue: [YourTextFieldHEre]*1

 

[essaytee]

This link sheds some light on the topic.

I've just checked it out on a sampe table and the following should achieve what you want (substitute for your table and field names)

ALTER TABLE tbl_test ALTER COLUMN TextField DOUBLE;

But, it's just as easy to modify the field directly.

I used sample text "0.123456789" and there was no loss or stripping of digits.

Steve.