Using SQL select statements I import data from an Access table into Excel.
The field size in Access = single. All the numbers only have 2 decimal places, however, when they are imported to Excel they end up being any number of decimal places.
For example:
6.93 comes out as 6.929999999993
Is there any explanation for this behaviour?
I'm guessing it's caused by floating point precision.
Basically, many numbers can't be accurately represented by binary within limited amount of binary digits.
Ex: Floating point approximation of 0.1 can be slightly greater than or less than 0.1
Have a read of links.
https://docs.oracle.com/cd/E19957-01..._goldberg.html
https://support.microsoft.com/en-us/...g-point-errors
http://www.exploringbinary.com/why-0...loating-point/
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Its also possible they are only formatted as 2 decimal places in what you see in the database but that the underlying data is more precise and Excel is just displaying the precision.
Do all the numbers show the same symptoms or only some?
Is it viable to just format them in Excel to show 2 decimals and/or to use a formula to round/truncate them?
Prior to the numbers going into the Access table they are rounded to 2 decimal places, therefore, there should be no reason that they would show as anything other than a number with 2 decimal places.
All of the numbers show the same symptom. 26 columns with numbers and all show the same symptom when imported into Excel.
All of the numbers are test scores between 0.00 and 99.99 so I have converted them to whole numbers using Int(number*100).
I did some reading this morning and my understanding now is that it has something to do with the field length being single and in the process of importing the numbers are converted to double. Or perhaps I misunderstood? It would be useful to read some sort of explanation.
That sounds about right. And is related to floating point conversion.
Basically, 6.93 actually has value of 6.929999828... stored in float, since it can't be converted to binary accurately.
That's the likely culprit.
In below example I've created table with 3 types.
Single = Number, Single, Decimal places 2
Double = Number, Double, Decimal places auto
Decimal = Number, Decimal, Precision 18, Scale 10.
Entered 6.93 in each of the field and queried using ADO into Excel.
Simple query without any conversion.
0.JPG
Please Login or Register to view this content.
Decimals added in Number format to show precision.
One way to get around it. Is by using Format().
This will return 6.93 as is. But will be returned as text. You can then use Excel to convert text to numbers and 6.93 will be in the cell.Please Login or Register to view this content.
Thanks for your explanations CK76, it has made things much clearer.
I used the single field size to save space so that I could fit more data into the table.
My understanding is that there is no difference between the space used by field size long and field size single so I will convert my numbers to long to save space and avoid the floating decimal point issue.
Another suggestion that I came across to overcome the problem is using the Cstr and CDbl functions
.Please Login or Register to view this content.
Last edited by anrichards22; 12-06-2017 at 06:14 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks