Numbers (decimals) changing when importing to Access 2010 from Excel

[dcanfield]

I have an Excel 2010 spreadsheet, where one of the columns is "Currency". I import that spreadsheet into Access 2010, but when I do, it changes the decimal values of the number in Access.



One example....When I look at the number in Excel, it shows $46.16 but when I look at the same number in Access (by clicking on the number in the Table, not just highlighting it, but actually clicking on it), I see $46.1599

And when I look at the same file/record in Access 2007, when I click on the number in the table, it just continues to show the correct $46.16, so it looks like it only happens in Access 2010.

I had known about the issue in Excel about the rounding problem, but as this number is coming directly pasted from an ascii Text File, with no operations performed on it, I did not think it would impact this issue. Even when I expand the decimals to 4 places in Excel, it still shows $46.1600, so no visibility of the changed number.

When I leave as just a number in Excel (without converting to Currency) so that it looks like 46.1600, it imports into Access without issue, and maintains the correct decimal places. And then if within Access I change the field type to Currency, it also keeps the decimal places correctly.

It appears that it only happens when I have a column in Excel that is set to Currency that is imported. As long as it is not set to Currency in Excel it imports correctly. I can then change it to Currency in Access, and all works, but that is a lot of extra work, that should not be needed.

And this only is happening in Access 2010, and NOT in Access 2007.

I checked, and I DO have SP 1 installed.

David

[JoeM]

Access experiences some of the same floating point arithmetic issues that Excel has, it has to do with how the Microsoft Programs store numbers, and how they are represented (see: http://support.microsoft.com/kb/111781). I don't know why you are seeing it in 2010 and not 2007, it might be an issue that they resolved in 2007 but haven't in 2010, or there might be something different between the two that you just aren't seeing (table/field properties).

It sounds like you figured out a workaround though, just change the format in Excel before importing it.

[dcanfield]

Here is the latest info……

I finally found someone at Microsoft that was willing to talk to me without requiring that I pay $100 for a support call.

They got me to a member the Access support team, who was willing and interested in checking out this issue. Working with him, I was able to show how the data file was changing decimal precision when imported into Access. We created a new file in Excel, with only one column, and only one row, with a manually entered value. When we imported it into Access, we could get the problem to replicate. He then created the same file on his own system and went to work testing it.

He called me back today with his results.

The data file imported WITHOUT PROBLEMS into Access 2003 and Access 2007.

But when he went to import the file into Access 2010 (and 2013), the same problem arose.

Based on that, he thinks that this is either a bug, or a change in the algorithm of the import routine that was implemented in Access 2010 (and still there for Access 2013). He placed a report internally to see if it can be fixed or resolved, but stated that it may take a while before anything is done with it.

In the meantime, he provided several workarounds that he has tested. A couple involved various ways of changing the data type either in Excel or Access, then changing back to Currency.

But the way that I think will work best for me, is that after the data is imported, to run an “Update” query with the following parameters:

FormatCurrency([amount],2)

That will force the values to change to the correct decimal precision.

Until Microsoft fixes this issue, there is nothing more that can be done, besides using the manual workaround for the problem.

Thanks to all who responded. It is very much appreciated!

David

[JoeM]

David,

Thanks for the update! You really took quite the initiative, and were very thorough.
Thanks to your efforts, hopefully Microsoft will fix this bug in an upcoming release/patch!