Prevent rounding #'s In Excel

[kpalazzi]

I have a large file in Access with many currency values. In Access I have these fields formatted with: number, fixed,2 decimals.

When I output the report to Excel, my totals are off by anywhere from .01 to .20 cents.

I think it is because Excel is rounding off my numbers. How can I prevent this from happening?

Kimberly Palazzi
kpal29@hotmail.com

 

[Hasit]

What are your cell formats set to? You should be able to get back the original amounts, as long as those amounts are actually being transferred intact (i.e. with 2 decimal places and not rounded up).

 

[kpalazzi]

My access fields are set to Double, fixed w/ 2 decimal places.

When it is exported to Excel, I am not sure how excel formats them but when I try to format them to Number, 2 decimal places, I don't get a change.

I found an article in the MS Knowledge base (q214118) that gave a solution of going to Tools/options/Calc tab and clicking "Precision As displayed" and then formatting the column to Number, 2 decimal places, but this doesn't change my incorrect calculation that is .06 cents off.
Kimberly Palazzi
kpal29@hotmail.com

 

[kpalazzi]

Ok, when I export from Access from the table, it is correct, when I export from the report, it is incorrect.

How can I make an export spec in Access to automate the export process?
Kimberly Palazzi
kpal29@hotmail.com

 

[Hasit]

You may consider using ODBC to extract the data directly from the Access tables into Excel, using MS Query. The command is DATA-GET EXTERNAL DATA in Excel.