X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Transferspreadsheet Zero In Double, Access 2013    
post Mar 2 2018, 02:20 PM

Posts: 937
Joined: 12-November 03
From: Iowa Lot

I used the transferspreadsheet method to import data from an Excel file (*.xls).
One of the fields contains floating point (double) values, and the receiving table field is formatted as double.
A value of 0.000024 is truncated as 0.0000.
There is no problem importing the higher remaining values in the source sheet.
Any explanations?

Until now, I didn't realize the native method couldn't handle low value doubles.

So as I was typing this it occurred to me to check the format of the source column in Excel - all values were displayed as 4 decimals, no format showing. I changed that column to General format, and the transferspreadsheet method worked.
So the question is, why the heck does the Excel display format matter to numeric transfers using docmd.transferspreadsheet?
My expectation is that the value in the cell would be transferred.
I understand, if you want numeric to transfer as text, but all values are numeric, and the receiving field is double.
Go to the top of the page
post Mar 2 2018, 03:13 PM

UA Admin
Posts: 34,688
Joined: 20-June 02
From: Newcastle, WA

Based on your description, the first thing I would do is to ensure that the Access fields actually contain 0s. You saw how formatting impacts the APPEARANCE of the values in Excel. Is it possible you also have FORMATTING applied to the table in Access which is displaying the same thing?
Go to the top of the page
post Mar 5 2018, 10:09 AM

Posts: 937
Joined: 12-November 03
From: Iowa Lot

Thanks for the suggestion, George.

I looked at that prior to finding the final solution, which was to format the Excel column.
The reason I even found the issue is that I had done a QA query to look for 0 and null values. These values were coming up 0 - a data issue, not Access formatting.

I also applied a custom import routine I had developed some time ago, rather than the transferspreadsheet method.

Both of those solutions (my import routine prior to formatting the Excel column, and formatting the Excel column prior to successful transferspreadsheet method) worked, so to me it is clear that the issue is in the transferspreadsheet method's interpretation of the Excel formatting.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd March 2019 - 07:41 PM