[AccessD] A2K - Adding decimal error

Reuben Cummings reuben at gfconsultants.com
Wed Apr 27 12:51:07 CDT 2005


I do that exact thing to calculate the amount payed.  This error is really
just an oversight on my part from several years ago.  I thought I had made
all numbers currency for these calcs.

However, I still didn't think an error would build up in addition of simple
decimals.

Anyhow, I have already programmed the data type change into the BEU (and
tested the results) so all my clients will get the fix next time they update
themselves.

Reuben Cummings
GFC, LLC
phone: 812.523.1017
email: reuben at gfconsultants.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of MartyConnelly
Sent: Wednesday, April 27, 2005 12:15 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2K - Adding decimal error


You will get this error with any series of long computer calculations.
The general way to resolve this,  is to round to the number of
significant digits desired
 then do each addition or division etc.

When working with money the number of signicant digits  is the the
smallest unit or 2 for decimal currencies
So you don't have to work this out for calculations.

 Here is a function I use for Rounding Currency,
Your accountant may use different rounding methods simple, banker's,
synchronous or asynchronous

It takes care of sign problem and allows precision selection
of currency value. Not all currencies are decimal based, old turkish lira

Function RoundIt( _
    x As Currency, _
    Optional Precision As Integer = 2 _
    ) As Currency

    RoundIt = Sgn(x) * Int(CDec(Abs(x)) * 10 ^ Precision _
    + 0.5) / 10 ^ Precision
      Debug.Print RoundIt
End Function

There is an entire branch of mathematics that deals purely with error
propagation.  It can give you
accurate guesses at what the final accuracy will be of mixed accuracy
calculations.

ACC2000: Round or Truncate Currency Values to the Intended Number of
Decimals
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210564

essentialy to round via bankers rounding (there are varieties of methods
for this, especially on rounding up or down on negative numbers)
so see this as well.

ACC2000: How to Round a Number Up or Down by a Desired Increment
http://support.microsoft.com/default.aspx?scid=kb;en-us;q209996

Simplest explanation of significant digits and rounding errors
http://www.newton.dep.anl.gov/askasci/math99/math99163.htm

http://www.rit.edu/~uphysics/uncertainties/Uncertaintiespart2.html
http://www.rit.edu/~uphysics/uncertainties/Uncertaintiespart1.html
http://teacher.nsrl.rochester.edu/phy_labs/AppendixB/AppendixB.html


Knuth has a very good analysis of floating point errors


Reuben Cummings wrote:

>A client came across this for me...
>
>One employee has 10 pay records on one day that are each .6 hours.  After
>gathering all the pay info I use a standard grouping query with a sum to
get
>the total hours.
>
>Rather than adding up to 6 hours, these 10 records resolve to
>6.00000023841858
>
>Why the strange decimals?
>
>Thanks.
>
>Reuben Cummings
>GFC, LLC
>phone: 812.523.1017
>email: reuben at gfconsultants.com
>
>
>
>
>
>

--
Marty Connelly
Victoria, B.C.
Canada



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com







More information about the AccessD mailing list