Correcting Decimal Error when Exporting from Access to Excel

**cricrazy** · 01-28-2009, 04:12 PM

When I export from MS access to Excel using a macro, all the numbers are getting decimal errors like

0.109 => 0.108999997377396
1.253 => 1.25300002098083

The numbers are having long unnecessary decimals.

I already have excel spreadsheet, with all these columns of long decimals. How to correct them?

1) Is there a worksheet function which corrects this decimal errors?
2) Is there a way to correct the macro to correct in while exporting it?

Thanks.

Please Login or Register  to view this content.

**Leith Ross** · 01-28-2009, 04:17 PM

Hello cricrazy,

It isn't an error. You simply need to set the number format for the Excel column to display only decimal places.

1.Select the Column
2. Right Click in column and select Format Cells...
3. Click the Tab labled Number
4. In the Category box click Number
5.Change the Decimal Places to 3
6. Click OK

**cricrazy** · 01-28-2009, 04:27 PM

Originally Posted by Leith Ross

Hello cricrazy,

It isn't an error. You simply need to set the number format for the Excel column to display only decimal places.

1.Select the Column
2. Right Click in column and select Format Cells...
3. Click the Tab labled Number
4. In the Category box click Number
5.Change the Decimal Places to 3
6. Click OK

I agree. But it only changes the way it changes the way excel displays the number. The number's decimal precision error remains the same.
I would like to correct that.
I hope I convey my point. Thanks for the reply.

**Leith Ross** · 01-28-2009, 04:35 PM

Hello cricrazy,

Internally all Excel decimal fractions are computed to 15 places. So, where's the error?

**cricrazy** · 01-28-2009, 08:56 PM

I have added the excel sheet. You can have a look at it. You can see the depth column and avg dia column. They don't have error but I suppose its precision floating point number error.

Thanks.
Chris.