Export to Excel Single/Float format

waka

Registered User.
Local time
Yesterday, 22:47
Joined
Jul 15, 2009
Messages
20
I have a couple of fields in my database that are formatted single or float. I've noticed that when I export the data to excel it changes these numbers. For example if I have 129.01 in a field (format=single) in excel it will show up as 129.009994506835. Why is it doing this and how can I get it to stop? Is it actually storing the long version in the database or just doing some sort of conversion once it comes into excel? I'm using 2007 for both applications though they are saved as the 2003 version (.mdb and .xls)
 
The value in the field is 129.009994506835
However it is formatted in the table to two dispaly decimal places.

Either update the fields in the table to the rounded value or round it in a query and export the query.
 
Use the Round() function to specify the number of decimal places you want before exporting.

Round(FieldName, 2)
 
Or impose a format on the exported cells, since Excel ALSO supports fixed-decimal formats. The trick is to recognize the need to do so.
 
Thank you for the responses. What I don't understand, however, is if the data is entered as 129.01 in the database, why isn't it stored as 129.010000000000? I tried running an update query to round the numbers to the decimal places. But when I export it to excel it still shows 129.009994506835. I just want to ensure that when I run calculations in access it is using the value 129.010000000000 rather than the altered version as this can introduce an error I don't want.
 
It only removes any extra leading zeros (after the d.p.), it doesn't remove other numbers unless you rounded up.
 

Users who are viewing this thread

Back
Top Bottom