We help IT Professionals succeed at work.
Get Started

Import rounded numbers into Access from Excel

514 Views
Last Modified: 2016-02-11
I have a file in Excel 2010 that has fields with numbers rounded to the nearest dollar.  The value has cents in it, but it is just formatted to only show whole dollars.  However, I wish to import the cents into an Access 2010 table as well.  I currently use the following command to import the worksheet:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
         strTable, strFile, True, wks.Name & "$"

For the most part, all of the values are being imported correctly.  However, when there is a column where the top several rows do not have any cents, it seems that when it will not bring in the cents in the subsequent records.  It will bring in the rounded number instead.  I am not 100% sure, but I think there is something in Access where it looks at the top rows to determine the datatype it is importing.  In this case, it seems that it thinks everything will be whole dollars and it ignores the cents.

I know that if someone manually formats the sheet to show 2 decimal places prior to importing, it will work fine.  But, I am trying to eliminate the need for any modification to the original dataset.  What is the best approach at doing this?

Thanks
Comment
Watch Question
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE