[AccessD] Rounding Error - A2K

Charlotte Foust cfoust at infostatsystems.com
Wed Dec 9 15:07:14 CST 2009


Are you working with singles or doubles and what precision.  All those things matter in Access.  Plus floating point values are notorious for adding stray decimal values even in addition, let alone multiplication.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Reuben Cummings
Sent: Wednesday, December 09, 2009 12:30 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Rounding Error - A2K

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




More information about the AccessD mailing list