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!