decimal errors when importing from Excel into Access

Question

I have an Excel 2010 spreadsheet, where one of the columns is "Currency". I import that spreadsheet into Access 2010. When I then do a "SUM" Query in Access, the resulting number is off by a penny. When I then export the contents of the Access Table, and place it back into an Excel spreadsheet, I find that instead of being only 2 decimal places, the Currency column is now up to 4 or 5 decimal places.

When I use the same spreadsheet in Excel/Access 2007, it works correctly.

I have just setup this new computer, that came with Office Professional 2010 pre-installed by Dell.

Any ideas?

thanks,

David

Answer 1

Thanks for your reply.  Good suggestion, but unfortuntely already checked that.  The field in Excel is "Currency", and as I am creating (not appending) the table in Access, it does also keep the field as a "Currency" datatype.

For example, when I look at the data in Excel, it shows $43.16 but when it is imported into Access, it then shows $43.1599 when I expand that number of decimals to 4 instead of the two that is initially displays.

Answer 2

What datatype is that field in Access?  Did you use Currency and NOT Double?  If not, you should.