Difference in value of entered data and sum (1 Viewer)

[GaP42]

Confused by this:
I have a sub form where I can record payments against an invoice. The total payments received are compared to the invoice total to determine if the bill is fully paid.

However I have found that a single payment of 49.95 shows the sum of payments as 49.950000763
I had Total Payments set to 2 decimal places and currency format in the subform - but could not find out why the payment did not result in the invoice being fully paid - as the values appeared to be equal. Changing to General Number format on the form revealed the discrepancy.

Thinking it may have been the data type for the Payment Amount I have the item capturing the payment as Number, Field Size: Single, Format Currency, decimal places 2. Looking at the data directly in the table all appears OK / entry of "49.95" is accepted ok and shows as $49.95 - however I clicked on this displayed value in the table and it shows: 49.9500007629395. Trying to edit this to remove the extra values was unsuccessful. No other values for other records in the table display the extra digits when editing directly on the table. Until I edited a payment value entering and entered value to 3 decimal places - (it is now showing value: 1.95000004768372 when editing).
I don't know what to do about this. What do you recommend? If I follow through on trying Integer, the amounts will be recorded in cents and the presentation of the values will require, I think, conversion to string, placing the decimal point and prefixing the $.

 

[GaP42]

Foolishness: The datatype is now set to Currency (which has an inherent Integer value). It is resolved... still checking.
Not trying various number data types with format currency.

 

[The_Doc_Man]

In general, when you display a value directly from a table to a form's control, particularly if it is a DOUBLE, the default format is GENERAL. You can see the round-off errors that occur due to truncation because as it happens, 0.01 (representing one penny) cannot be expressed exactly when using binary fractions. So the math routines come as close as they can but they have to stop when they run out of bits.

The factors for 0.1 (1/10) are 1/2 and 1/5, and of course 1/2 is EASY to express in binary. But 1/5 is irrational in binary and therefore can never be exactly represented. The fraction 0.1 when converted to binary is something like 0.00011001100110011... (repeating pattern ad infinitum). And then you multiply 0.1 x 0.1 to get down to the penny (0.01) and jumble up the repeating bits even more.

Your solution to use a CURRENCY data type is probably the best and easiest course of action unless that large data type takes up too much room due to having literally hundreds of thousands of records. CURRENCY is twice the size of DOUBLE in storage, but can do dollars and cents pretty easily without that rounding vs. truncation issue.

Another solution is to drive things from a query that uses a format statement or to change the display format of the control. But your comment "could not find out why the payment did not result in the invoice being fully paid" simply means you tried to compare two computational numbers with different origins (and thus different truncation anomalies) when you SHOULD have been comparing two numbers using the ROUND function, or convert both numbers to strings with two decimals and compare them as numeric strings.

 

[GaP42]

In general, when you display a value directly from a table to a form's control, particularly if it is a DOUBLE, the default format is GENERAL. You can see the round-off errors that occur due to truncation because as it happens, 0.01 (representing one penny) cannot be expressed exactly when using binary fractions. So the math routines come as close as they can but they have to stop when they run out of bits.

The factors for 0.1 (1/10) are 1/2 and 1/5, and of course 1/2 is EASY to express in binary. But 1/5 is irrational in binary and therefore can never be exactly represented. The fraction 0.1 when converted to binary is something like 0.00011001100110011... (repeating pattern ad infinitum). And then you multiply 0.1 x 0.1 to get down to the penny (0.01) and jumble up the repeating bits even more.

Your solution to use a CURRENCY data type is probably the best and easiest course of action unless that large data type takes up too much room due to having literally hundreds of thousands of records. CURRENCY is twice the size of DOUBLE in storage, but can do dollars and cents pretty easily without that rounding vs. truncation issue.

Another solution is to drive things from a query that uses a format statement or to change the display format of the control. But your comment "could not find out why the payment did not result in the invoice being fully paid" simply means you tried to compare two computational numbers with different origins (and thus different truncation anomalies) when you SHOULD have been comparing two numbers using the ROUND function, or convert both numbers to strings with two decimals and compare them as numeric strings.

Thanks - I appreciate the dive into the explanation as to what was happening. Re ROUND function - also found I had to apply this to the compare of the total invoice and total payments due to application of % discounts resulting in fractional cents in the invoice total..

 

[Pat Hartman]

People get confused because Currency is both a data type and a display format. I use the currency data type for any numeric value that does not require more than for decimal digits. Then I format it as currency, percent, whatever.