Percent Field

[Eljefegeneo]

Another thing that is not working correctly for me is the use of the Percentage format for a number. I have a field on a form Commission where the criteria are:
Field Name: Commission
Format: Percentage
Row Source: "0";".15";".10"
Row Source Type: Value List
Format: Percent
Decimal Places: 2

The form has a combo box that permits the user to select one of these three values.

A calculation is made on a query where the total is:

  TotalCost: NetRate*(1-[Commission])

If the commission is zero, then the calculation results in a number with two decimal places. This is what I want. But if the commission is .10 or .15 then the calculation results in a number with that has 12 decimal places. This is crazy! For example, one calculation has a [NetRate] of $100 and a [Commission] of 10%. The calculation results in 89.9999998509884.

I have performed the same calculation with NetRate*.9 and it comes out $90. But when I calculate the multiplier 1-Commission I get 0.899999998509884. So I know it has to be with the setting of the Row Source, but it looks OK to me.

What is going on? I know I can round and correct this, but why would this result occur?

 

[Galaxiom]

Arithmetic in a system where decimal numbers are represented using binary can be problematic. The solution is to preform the calculations using a number type that is designed to handle it.

Decimal is stored as a scaled integer having the significant digits as an integer and the number of decimal points as a scaling factor. Currency is a type of Decimal number where the scale is fixed to three decimal places.

Number can be converted to Decimal before processing:

 TotalCost: CDec(NetRate)*(CDec(1)-CDec([Commission]))

 

[The_Doc_Man]

Here is the problem with 10%. In decimal notation, this is a fixed number of decimals and you can predict its value easily. 10% = 0.1000000...00000 ad infinitum so in decimal, it comes out even.

Binary systems? Not so lucky. 10% = 1/10 = 1 / (2 * 5 ) - and that factor of five is the beast. If you do the math in binary, 1/10 (decimal) is 1/1010 (binary). That fraction when divided out in decimal is 0.00011001100110011001100...11001100 ad infinitum (or it is something close to that, anyway.) So that 89.99999... that you got is because 10% isn't exact. Which is why, as Galaxiom suggests, you should use a scaled number system where 10% is still an exact number.

 

[Eljefegeneo]

Thanks guys, learn something new every day.