[AccessD] A2K - Adding decimal error

Don't forget you must round to the precision needed in the intermediate
calculations to avoid bias or roundoff errors, regardless of the precision
of the variables used or compiler.
After doing one billion additions of .01 or say 1 cent
The difference in calculated values can be seen when you apply the
intermediate rounding function.
Applying this method will keep demented accountants out of your hair.

Final Total unrounded decimal     accurate  intermediate rounded total
 9999999.82515867                               10000000

 Sub CalcSum()
  'this may take several minutes to run
  ' add 1 cent 1 billion times and apply rounding
                        Dim i As Long
                        Dim iMax   As Long
                        Dim fSum  As Double
                        Dim fSuma  As Double
                        Dim fPenny As Double
                        i = 0
                        fPenny = 0.01
                        iMax = 1000000000
                        fSum = 0#
                        fSuma = 0#
                        For i = 0 To iMax - 1

                           fSum = fSum + fPenny
                           fSuma = fSuma + fPenny
                           'intermediate round to 2 decimal point precision
                           fSuma = Round(fSuma, 2)
                        Next i
Debug.Print fSum; fSuma
 End Sub


Gustav Brock wrote:

>Hi Charlotte
>
>It fails all right, but not you have to go beyond 16 mio.:
>
>Public Sub TestSingle()
>
>  Dim sng As Single
>  Dim lng As Long
>  
>  For lng = 0 To 100000000
>    If lng <> sng Then Exit For
>    sng = sng + 1
>  Next
>  Debug.Print lng, sng
>
>End Sub
>
>It runs to:
> 16777217      1.677722E+07 
>
>/gustav
>
>  
>
>>>>cfoust at infostatsystems.com 04/27 7:17 pm >>>
>>>>        
>>>>
>I've seen it with addition as well.  You can build a test in code and
>loop multiple times adding 1 to the integer of a floating point and
>testing the mod to see if there is a remainder.  Believe me, it will
>occur.
>
>Charlotte Foust
>
>
>-----Original Message-----
>From: Gustav Brock [mailto:Gustav at cactus.dk] 
>Sent: Wednesday, April 27, 2005 9:37 AM
>To: accessd at databaseadvisors.com 
>Subject: RE: [AccessD] A2K - Adding decimal error
>
>
>Hi Reuben
>
>Charlotte a guy? You better be careful!
>
>You mostly see this with subtraction (which includes addition of
>negative values); I've never seen it with addition of positives.
>
>If you can't change the source data type be sure to wrap the values in
>CCur(). If you need more than the four decimals of Currency you may
>choose Double but round the results whenever possible.
>
>/gustav
>
>  
>
>>>>reuben at gfconsultants.com 04/27 6:26 pm >>>
>>>>        
>>>>
>You guys nailed that one.  HoursWorked is a single.  I never
>considered
>it would cause a problem with addition.  Of course, after all the work
>I
>did on payroll calculations I can't beleive it was still a single.
>
>Thanks.
>
>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 Gustav Brock
>Sent: Wednesday, April 27, 2005 10:53 AM
>To: accessd at databaseadvisors.com 
>Subject: Re: [AccessD] A2K - Adding decimal error
>
>
>Hi Reuben
>
>He is probably adding and subtraction 0.1 hour as well and you are
>using
>data type Single for that. Change to data type Currency ...
>
>/gustav
>
>  
>
>>>>reuben at gfconsultants.com 04/27 5:38 pm >>>
>>>>        
>>>>
>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