Access export query to Excel - certain decimal numbers are changed (not just formatted)

[jessegregory]

Hello all - I'm a relative noob to Access but have a long history using office. I have Access 365 queries I want to export to excel 365, which I am able to do via external data > excel button or using vba via DoCmd.transferspreadsheet or CopyFromRecordSet. My problem is that number values in these queries that have a decimal value (not integers) that does not end in 5 are being changed slightly during the export process. Said another way, integers and numbers like 0.5, 1.5, 2.25, 3.335 are not changed. But numbers like 0.1, 1.2, 3.44, etc... are changed in the export. These numbers round to original value when kept to a few decimal places, but it is not the same number.

This first screenshot shows the view of the query in with particular value that will be affected circled. The number is exactly 4.6.





But when I export to excel and open in excel, this number is changed to a slightly smaller number "4.59999990463257"



This will happen to any number that has a decimal value not ending in 5. Sometimes it changes to a slightly larger number, sometimes a slightly smaller number, and in all cases it will round to the original value if only keeping a few decimal places.
Has anyone else experienced this and know why this happens? I am dumbfounded why such a thing happens. I already have workarounds for the issue, like formatting the query and rounding numbers, so I can solve the problem already. I am just curious of the root cause of this and if there is something to prevent the change from occurring in the first place. This only happens when exporting to excel, and does not happen when exporting to text. This also happens if I export the tables the query is referencing.

I hope I was descriptive enough but please do let me know
Thanks in advance for any insight!

[pbaldy]

If the data type is single or double, this can happen. Those are floating point data types.

[jessegregory]

If the data type is single or double, this can happen. Those are floating point data types.

That fixed it! Changed to decimal with 3 places to right of decimal point (which is perfect for this data) and no issues now. Thank you!
Out of curiosity, if you are able to explain it without too much effort, why would that happen with the floating point data types in the conditions I mention in the original post?

[jessegregory]

That fixed it! Changed to decimal with 3 places to right of decimal point (which is perfect for this data) and no issues now. Thank you!
Out of curiosity, if you are able to explain it without too much effort, why would that happen with the floating point data types in the conditions I mention in the original post?

After I asked for the explanation, down below I saw the link to the following thread: https://www.accessforums.net/showthread.php?t=64910, which happens to be my exact issue. I did search before posting, but obviously didn't look hard enough. In it, there are some links to explain the issues further:
http://www.cpearson.com/excel/rounding.htm
http://stackoverflow.com/questions/2...-places-does-a

I can't say I've fully absorbed them yet, but hopefully they will lead to the understanding of the root cause of the issue.

[pbaldy]

Glad you found those, I'd add this one, the bit on floating point errors:

http://allenbrowne.com/round.html

I can't recall the last time I used either of those data types, I use decimal or money (currency in Access).