rounding in MS Access

[Ted]

I am having a problem with rounding in Access 2000 VBA. I
have read the problems on here with floating point, and
even after trying the tips here I still have the problem.
Here is the formula in VBA:

Var1 = Round(1.25 * Var2, 2)

The value of Var2 is '.5'. After rounding, the result of
this should be '.63', and it keeps coming out to '.62' no
matter what I do. Var2 is defined as currency; I
originally defined as single. Same with Var2 -- Var2 is
also defined as currency; I originally defined as single.

What does it insist on rounding down when it should round
up?

 

[Bas Cost Budde]

Var1 = Round(1.25 * Var2, 2)

The value of Var2 is '.5'. After rounding, the result of
this should be '.63', and it keeps coming out to '.62' no

What does it insist on rounding down when it should round
up?

Can be overcome by adding .005 Maybe that is not what you want?

I cannot really test because Access 97 has no Round.

 

[Immanuel Sibero]

Hi Ted,

Check out this link:

http://www.mvps.org/access/modules/mdl0054.htm

Also, search and read up on "banker's rounding".

HTH,
Immanuel Sibero

 

[Wayne Morgan]

The Round function does what is know as "banker's rounding" or "scientific
rounding". At .5 it rounds to the nearest even number. This way you round up
1/2 of the time and round down 1/2 of the time, theoretically reducing the
changes in the results caused by rounding.

0 No change
1-4 Round Down
6-9 Round Up

So far this is fairly even, 4 round up and 4 round down. To have .5 alway
round up, you have 5 round up and 4 round down. So to elimanate this, the
above procedure is applied. However, this isn't how most folks in the US do
their rounding. To alway round up at .5, create your own Public Function in
a code module and call it instead of the built-in Round function.

Example:
Public Function MyRound(curInput As Currency) As Currency
'This will round to 2 decimals
Dim curOutput As Currency
curOutput = Int(curInput * 100 + 0.5) / 100
MyRound= curOutput
End Function

 

[Ted]

Thanks to all of you for explaining this. I had never
heard of "bankers rounding", but it makes sense. It's
been a long time since I had to deal with this kind of
math, and I had thought that generally 5 is always rounded
up!

After talking to the user they are fine with "bankers
rounding"; they just wanted something consistent, but it's
good to know about the module in case they wanted 5 to
always round up.

Ted

 

[Ted \(again\)]

By the way it would be NICE if Microsoft changed their
help documentation to state HOW the rounding works! (hint,
hint!) As one user points out, I think most people in the
U.S. are used to the concept of 5 always rounding up!

Ted

 

[Immanuel Sibero]

Good point!

We don't work banker's hours, why should we use banker's rounding?

Immanuel Sibero

 

[Mike Sherrill]

Public Function MyRound(curInput As Currency) As Currency [snip]
curOutput = Int(curInput * 100 + 0.5) / 100

[snip]

This isn't a good way to round Currency. It doesn't cover the full
range of values, and it rounds some negative numbers the "wrong" way.
Try this expression instead.

CCur(Format(curInput, "0.00"))