I'm pretty confused about the rounding function not working as expected when used in queries. Update, I think i found the problem, listed below, but am not sure why it should have worked this way
Basically, I have a value [Amount] that is type Single, and often has 5 digits usable precision, but in certain queries I want to round it to two digits, other times keep it to 5. What I'm finding is that even if [Amount] has only two decimals actual data, the query is returning 12 or 13, almost like its working in reverse, where it "unrounds" it to a slightly lower number...see below. I even tried to use an integer rounding function that first multiplies the value by 100000 then takes the integer, then divides by 100000, and I see similar behavior: it is almost like Access is storing the data in a different precision than its actually presenting it
Never seen this before, not sure how to correct for this and make it as expected. BTW, if I use the same query and make a table from it, these 12 digit values are put into the new table.
Any ideas appreciated, cause I'm trying to fix my math to 5 digits in some places and this inaccuracy is making it impossible to know what the real value should be. I put a few sample values above the initial 2 digit number so you can see how confusing the behavior is.
SQL:
SELECT amount, Round([amount],2) AS Round_2_Digits, Round([amount],5) AS Round_5_digits, Int([amount]*100000)/100000 AS Integer_5 digits from FCS
amount Round_2_Digits Round_5_digits Integer_5 digits
95.88 95.879997253418 95.879997253418 95.87999
95.88001 95.879997253418 95.880012512207 95.88001
95.881 95.879997253418 95.8809967041016 95.88099
95.889 95.8899993896484 95.8889999389648 95.88899
Update: as I was writing this, I wondered if the Single data type was the problem, and when I changed it Double, first the source data [Amojnt] definitely changed, as shown below, then when I fixed the source [Amount] to the original, the rounding function worked perfectly. In fact it worked even with the weird data, since it was such a small amount that it was off.
So is the Single data type the culprit? I was using it because I was trying to minimize data storage, but clearly Double is the way to go. anyone know why? thanks!
Here is the updated query after I changed the data type to double:
amount Round_2_Digits Round_5_digits Integer_5 digits
95.879997253418 95.88 95.88 95.87999
95.880012512207 95.88 95.88001 95.88001
95.8809967041016 95.88 95.881 95.88099
95.8889999389648 95.89 95.889 95.88899
Here is the query after I reset the original data to what it should have been
amount Round_2_Digits Round_5_digits Integer_5 digits
95.88 95.88 95.88 95.88
95.88001 95.88 95.88001 95.88001
95.881 95.88 95.881 95.881
95.889 95.89 95.889 95.889