[AccessD] Rounding Error - A2K

Never use Single Precision floating numbers!

Function WhyUseDoubles()
Dim a As Double
Dim b As Double
Dim d As Single
Dim e As Single
Dim x As Double

a = 332215.5
b = 0.258099
c = 332215.5
d = 0.258099
x = a * b
Debug.Print "Double Precision: "; x
x = c * d
Debug.Print "Single Precision: "; x
End Function


:-)

-- 
Stuart

On 9 Dec 2009 at 15:29, Reuben Cummings wrote:

> Thanks.  Interesting stuff.
> 
> I wrote a quick function that merely evaluates the 3rd decimal and either
> adds .01 or doesn't in order to test the results I was getting.
> 
> Turns out the error is not in the Rounding.
> 
> THE ERROR IS IN THE MATH TO GET THE NUMBER TO BE ROUNDED.
> 
> 
> In Access 332,215.50 * .258099 = 85744.48492
> In Excel or on a calculator...         85744.48833
> 
> The other problem...
> In Access 98518.68 * .253696 = 24993.79451
> In Excel or calculator...             24993.79504
> 
> If I "Hard Code" the calc into a query I get the .48833.
> But if I let the query do the math to the 332215.50 and then multiply I get
> .48492
> 
> I've looked for extra decimals, but see nothing.
> 
> Any ideas?
> 
> Reuben Cummings
> GFC, LLC
> 812.523.1017
> 
> 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com 
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> > Gustav Brock
> > Sent: Wednesday, December 09, 2009 1:55 PM
> > To: accessd at databaseadvisors.com
> > Subject: Re: [AccessD] Rounding Error - A2K
> > 
> > 
> > Hi Reuben
> > 
> > Round in Access is known to be buggy.
> > If you are interested in rounding and how to optimise this 
> > regarding speed while still being accurate, read on here:
> > 
> >   http://www.xbeat.net/vbspeed/c_Round.htm 
> > 
> > Note that the absolutely simplest (but slowest) method to 
> > perform 100% correct (mathematical) rounding is ... Format!
> > 
> > /gustav
> > 
> > 
> > >>> accessd at gfconsultants.com 09-12-2009 19:39:40 >>>
> > Can anyone explain to me, given the following two sets of 
> > numbers why I get
> > the answers I do in Access?
> > I'll also provide the answers to the same numbers and 
> > equations in Excel.
> > Access simply seems to be rounding wrong.
> > And these are only 2 out of 22.  The other 20 are correct.
> > 
> > =============================================
> > .47
> > 206104.58
> > 118507.98
> > 3312.20
> > 16
> > 4274.27
> > Total = 332215.50
> > 
> > % = .258099
> > 
> > If I do Round(Total*%,2) I get 85,744.48
> > In excel I get 85,744.49
> > 
> > The number, before rounding is 85744.48833
> > 
> > =============================================
> > 
> > The 2nd set is 
> > 66986.81
> > 29694.41
> > 1837.46
> > Total = 98518.68
> > 
> > % = .253696
> > 
> > Round(Total*%,2) in Access gives me 24,993.79
> > 
> > Excel gives me 24,993.80
> > 
> > Before rounding it is 24993.79504
> > =============================================
> > 
> > Why the rounding errors?
> > 
> > Reuben Cummings
> > GFC, LLC
> > 812.523.1017
> > 
> > 
> > 
> > -- 
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com