# Thread: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

1. ## Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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

2. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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.

3. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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>

4. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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.

5. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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.

6. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

Sorry for my confusion, I

7. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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

I

9. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

What happens if you change it to:

<code>
TownAmount: Round(Int([Fee]*53)/100+[Local],2)
</code>

10. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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

11. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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.

12. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

<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.

13. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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>

14. ## Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

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' worth

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•