Converting text to numbers

[exaccess]

Hi All,
I have been scanning the postings and could find things similar to my problem but not exactly the same issue. Here is the problem. I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion? Help please.

 

[exaccess]

Yes I have tried that. But I am still getting 21.6 instead of 21.60 or 59.8 instead of 59.80. I am probably missing an obvious point but what. Also I have another related question which is giving the final result of the program. I shall export these numbers to a spreadsheet. How can I set the formatting of the cells of the spreadsheet so that I get exactly the display I want.

 

[JHB]

Open the table in design view:
Set the Format to Fixed and Decimal Places to what you want.

 

[DrPat]

Hi Exaccess,

This can be a very useful tool as a public function.

I'm at work and don't have time to fiddle with this, but it seems you need to:
1. Find the number of characters after the decimal in your string
2. Coerce the string with CDbl() while setting the format to match the number of decimal places discovered in step 1.

to get you started, I found a public function to find the number of decimal places here.

Good luck,
/Pat