Reuben Cummings
accessd at gfconsultants.com
Wed Dec 9 20:16:22 CST 2009
Stuart and Charlotte, I'll play with the single/double issue. The dollar amounts are all currency. The percentage (.25xxx) is a single. Not sure why as this is the first time I've ever used a single. All these years and I've never used single and the first time I do it BITES me. I wanted to use currency for the percentage, but the state defines these rates to 6 decimals. Reuben Cummings GFC, LLC 812.523.1017 > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > Charlotte Foust > Sent: Wednesday, December 09, 2009 4:07 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Rounding Error - A2K > > > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >