[AccessD] A2K - Adding decimal error

Hi Marty

That's right, but Charlotte mentioned adding 1 not 0.01.

However, if anyone at this point still are confident using Double (or <
gosh> Single) for decimals, I modified your test.
This is scary. Notes follow the function:

Sub CalcSum()
  
  ' This will take a fraction of a second to run.
  ' Add 1 cent up to 1 billion times and apply rounding.
  ' Exit at first encountered error.
  
  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)
    ' Exit if an error has occurred.
    If fSum <> fSuma Then Exit For
  Next i
  
  Debug.Print fSum; fSuma, fSum - fSuma

End Sub

Now, take your guess - how many loops will run? Ten thousand? 
You'll be in for a surprise!

Note that even though fSum and fSuma display the same result, they
don't compare.
You have to do the fSum - fSuma subtraction to have the error
displayed.

/gustav

>>> martyconnelly at shaw.ca 04/27 10:32 pm >>>
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