Results 1 to 14 of 14

20050804, 18:53 #1
 Join Date
 Jun 2001
 Location
 Niagara Falls, New York, USA
 Posts
 1,878
 Thanks
 0
 Thanked 0 Times in 0 Posts
Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
I have an Append Query with the following expression in one of the columns:
TownAmount: Round(Int([Fee]*53)/100,2)+[Local]
All fields are currency with auto decimal places
The resulting field in the table has the following value:
TownAmount
$2,033.38
It should be $2,033.37
When I click on the $2,033.38 value it changes to $2,033.375
What do I have to change to get $2,033.37 as the correct value?
Thanks, John

20050804, 18:57 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
Why should 2,033.375 be rounded to 2,033.37? The standard rule is that if the next decimal is 0, 1, 2, 3, or 4, the last displayed decimal should be rounded down; if the next decimal is 5, 6, 7, 8, or 9, the last displayed decimal should be rounded up. So rounding 2,033.375 to 2,033.38 is correct.

20050804, 20:59 #3
 Join Date
 Nov 2004
 Location
 Wilmington, North Carolina, USA
 Posts
 1,196
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
Unless you're a banker, then the customers lose that $.05....ever seen the movie Office Space? Sorta like that. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
____________________________
Jeremy
"If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." Richard Clarke

20050804, 21:13 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
Perhaps it's a question of terminology: rounding can be up or down, truncating is always down (probably because the difference goes into the bank's trunks <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
Round(2033.375, 2) = 2033.38
Int(2033.375 * 100) / 100 = 2033.37
In fact, Round confusingly uses a method called Banker's Rounding: 2033.375 becomes 2033.38 and 2033.365 becomes 2033.36. If the next digit is 5, the last decimal will be the nearest even digit.

20050805, 12:56 #5
 Join Date
 Jun 2001
 Location
 Crystal Beach, FL, Florida, USA
 Posts
 3,430
 Thanks
 1
 Thanked 33 Times in 33 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
Why is $2,033.37 the "correct" value? One problem is that you round, then add to this the value in [Local]. If [Local] has 3 decimal places, then your result has 3 decimal places. You need to round AFTER you've done all the math.
Mark Liquorman
See my website for Tips & Downloads and for my Liquorman Utilities.

20050805, 15:28 #6
 Join Date
 Jun 2001
 Location
 Niagara Falls, New York, USA
 Posts
 1,878
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
Sorry for my confusion, I

20050805, 15:34 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
John, you still haven't told us why you want 2033.375 to be rounded to 2033.37.
If you want to truncate the end result of TownAmount to two decimal places, you should use
TownAmount: Int(([Fee]*53+[Local])*100)/100

20050805, 16:44 #8
 Join Date
 Jun 2001
 Location
 Niagara Falls, New York, USA
 Posts
 1,878
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
I

20050805, 16:49 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
What happens if you change it to:
<code>
TownAmount: Round(Int([Fee]*53)/100+[Local],2)
</code>Legare Coleman

20050805, 18:02 #10
 Join Date
 Jan 2002
 Location
 Brookings, South Dakota, USA
 Posts
 449
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
I don't know if this will help, but Helen Feddema has an archived Access Archon (#94) on her site which deals with rounding.
Helen Feddema's web site<IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
"Those who dance are considered insane by those who can't hear the music"  George Carlin

20050805, 20:07 #11
 Join Date
 Dec 2000
 Location
 Sacramento, California, USA
 Posts
 16,775
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
I never encountered that kind of rounding when I was a banker, John, but your formula doesn't make sense, since any number * 100 then divided by 100 is going to give you exactly the same number you started with! If it doesn't appear that way in the query, it's because of the way you formatted the field. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Currency fields have 4 decimal places and truncate beyond that.
Charlotte

20050805, 20:46 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Int(n * 100) / 100 is a valid way to truncate n to 2 decimal places.

20050805, 20:50 #13
 Join Date
 Dec 2000
 Location
 Sacramento, California, USA
 Posts
 16,775
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
Yes, it is, but in glancing at John's initial post, I didn't look closely enough and missed the Int function in there. Mea culpa. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
Charlotte

20050806, 23:27 #14
 Join Date
 Jan 2001
 Location
 Pennsylvania, USA
 Posts
 144
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Help With Rounding? ((a2k (9.0.6926) SP3 Jet 4.0 SP8) )
It would seem that the term "Bankers Rounding" is not really used in the content of its' name. I signed myself into an Accounting/Bookkeeping Class, the instructor gave us 16 variations of the same calculation, and said these are "Bankers Rounding". Almost had me believing that 2+2=3.
If someone wants to do some creative math they call it "Bankers Rounding".
Some folks round up and some folks round down. Some round to the nearest number.
As a general rule we ( I Believe ) accept that .564 would round to .56 and .566 would round to .57 and .565 would and can go either way up or down.
This becomes' a night mare at times when you have to deal with it, you are assured to be off by .01.
For what its' worththreecrow
Don't make excuses. It's what you do, not why you didn't.