Single Numbers converted to Double

[chris]

In a table I was keeping numbers (Single)

ex. 0.00001, 0.005 etc.

When I inserted them in a Double field, numbers where converted

0.001 to 0.00100000004749745

Users sees number on form with E included in it.

Can I change my numbers in Double format back to the original value ?

Thanks a lot in advance.

 

[Allen Browne]

Chris, the Double and Single data types are floating point numbers. Most
fractional numbers are not stored precisely, so when you convert you have
accuracy issues. Details:
http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

To see what happens, add a new field to your table, of type Single. Use an
Update query to popuate this field with the values from your Double. Does
this get you back to where you were?

 

[chris]

Thanks Allen for your message.

Value in my original Single field is 0.001
Value in Double field is 0.00100000004749745
Value in new Single field (updated with Double) is 0.001

I tried to use CSng for updating the Double field but no result.

Is there any way for updating my double field and receiving 0.001 value ?

 

[Allen Browne]

Chris, follow up the link I posted to explain what's going on with the
accuracy issue.

There are a couple of workarounds. One is to disguise the problem by
printing only a limited number of digits. For example, you might set these
properties for the text box:
Format Fixed
Decimal Places 3
It will then display the value rounded to 3 places, i.e. 0.001.

The other alternative it to avoid the floating point data types. If you need
no more than 4 decimal places, use a Currency type field. This is a fixed
point data type, and does not have the floating point issues. If you need
more than 4 places, you could use a Decimal data type, scaled to the number
of places you need. I suggest you do this only if you really need to,
because Access has some problems with this data type, e.g.:
http://allenbrowne.com/bug-08.html

 

[chris]

I cannot avoid using and 4 digits (currency) is not enough :-(

I will use Format(num,"0.0#######")

Thanks a lot!