Table field automatically changes the number entered. How can I stop that?

[PeakH]

I have a table tblOrderDetails. In that table is field PricePerPack. I enter information through a form frmOrderDetails. Every time I enter a number in that box, it saves a slightly different number.



Ex: I recently tried to enter the price 0.20 but the number saved was 0.200000002980232
Or 1.66 becomes 1.6599999666214
It does not seem to happen with whole numbers or numbers ending in *.50

I have no idea why this is happening and when I try to edit the number slightly so that it rounds in the other direction it changes back. If I try editing the number drastically, it saves a new number slightly different from the new on entered.

This only becomes an issue on large orders where the total price adds up to a few cents different from what the actual total is, but that can add up a lot over the course of a month or year making our books slightly off leading to a long arduous search for the culprit orders.


This field is datatype Number (size: single ; format: currency ; decimal places: 2)
The form box is also set to currency with 2 decimal places.

[JoeM]

The way that Access and Excel store numbers uses a binary system, which can cause this "floating arithmeric" error.
See here for a description and correction: http://allenbrowne.com/round.html
(sounds like you should be using the "Currency" type instead of a "Number" type with Currency format).

[PeakH]

Thank you!

I changed the type to currency which did get rid of the ungodly number of decimals, yet did not change the total being incorrect.
The article proved to be very helpful! The database was rounding two different ways, one way to display the number and another way to calculate it so the total did not match what was showed. I used Ken Getz' rounding function to change how it rounded in the calculation, and that was all I needed!