Is there a PROPER round function to use in ACCESS?

[Savvoulidis Iordanis]

I can't believe that ACCESS doesn't have a round function that actually
ROUNDS.
The one that exists does not work OK.
The one that I found on the news has some problems and follows:

Public Function RoundIt(adbl_number As Double, ai_decimals As Integer) As
Double
RoundIt = CInt(adbl_number * (10 ^ ai_decimals) + 0.5) / (10 ^
ai_decimals)
End Function

It rounds the number 2.94 to 3 using one decimal and not to 2.9

Is there any mathematician out there to help?

 

[Van T. Dinh]

What's wrong with the in-built Round() function?

From A2K2 Debug window:

?Round(2.94,1)
2.9

For the UDF RoundIt, there is a mistake: change the CInt to Int and you
should get the correct result:

?RoundIt(2.94, 1)
2.9

 

[Mike Painter]

I can't believe that ACCESS doesn't have a round function that
actually ROUNDS.
The one that exists does not work OK.
The one that I found on the news has some problems and follows:

Public Function RoundIt(adbl_number As Double, ai_decimals As
Integer) As Double
RoundIt = CInt(adbl_number * (10 ^ ai_decimals) + 0.5) / (10 ^
ai_decimals)
End Function

It rounds the number 2.94 to 3 using one decimal and not to 2.9

Is there any mathematician out there to help?

Access uses the same rounding that banks have used for hundreds of years.

When summing it is *never* less precise and almost always more precise.
Assuming the error caused by translating between decimal and binary is
random, your way rounds down 4 out of 10 times and up 6 out of 10 times. It
should be 50-50.

The "5" is the problem. Bankers rouonding looks at the preciding digit and
rounds one way if odd and the other if even.

 

[User]

Access uses the same rounding that banks have used for hundreds of years.

When summing it is *never* less precise and almost always more precise.
Assuming the error caused by translating between decimal and binary is
random, your way rounds down 4 out of 10 times and up 6 out of 10 times. It
should be 50-50.

The "5" is the problem. Bankers rouonding looks at the preciding digit and
rounds one way if odd and the other if even.

Using a double, there is no way to round to something like 2.9
You could multiply by 10 to get 29.4, roudn to 29 then divide by 10 to get
2.9, but that 2.9 may in fact be something like 2.8999999999.
This is arithmetic, not mathematics. You have to understand binary
representations

 

[Savvoulidis Iordanis]

How come such a wide spread programming tool like Access, DOES NOT HAVE a
proper round function, though?
(anybody from Microsoft could give us some explanation on this...)

 

[Van T. Dinh]

You keep posting WITHOUT reading replies ...

The problem is a about a foot in front of the VDU.

 

[Larry Linson]

How come such a wide spread pro-
gramming tool like Access, DOES
NOT HAVE a proper round function,
though? (anybody from Microsoft
could give us some explanation on this...)

I am anybody, but not from Microsoft, though they did recognize me for
helping their user community. I can try to give you an explanation, though:

A wide spread development tool like Access _does_ have a proper rounding
function (it is called "banker's rounding"). If it is not the one you
prefer, you can write your own... or, if you'd rather not have to exert the
effort write your own, you can search at http://groups.google.com and you'll
find that some other forms of rounding have been coded and posted -- don't
expect any support from the people who wrote them or posted them, though!

Larry Linson
Microsoft Access MVP

 

[User]

Aside from the built in Access rounding function, one reason for not having
more/others is that it is so easy to write on your own. Just one line and
some Cint or other such conversions. That's why, for example there is no
built in function such as f(a, b, c) = a*b +c

 

[Savvoulidis Iordanis]

Well, try round(2.5,0) and round(3.5,0)

The first should give 3 and the 2nd shoud give 4
But the 1st gives 2 instead

Does it have to do with any Access patches?
TIA

 

[Van T. Dinh]

Please re-read Larry Linson's post and my modification for the UDF RoundIt
you posted originally.

*Proper* rounding depends on the rounding definition you use. When I was in
high-school, we always rounded *down* the 0.5 (which was *proper* as far as
we concerned). When I started full-time work, the office insisted on
rounding *up* the 0.5 (which was *proper* for that office). Access happens
to use Bankers' Rounding method (which is *proper* for bankers and Access
implementation of rounding).

Small tweaks using a tiny bias should modify rounding to whichever way you
like or use the UDF RoundIt() as modified

 

[John Vinson]

Well, try round(2.5,0) and round(3.5,0)

The first should give 3 and the 2nd shoud give 4
But the 1st gives 2 instead

Does it have to do with any Access patches?

No.

It has to do with the definition of rounding, which Van and others
have REPEATEDLY explained, and which you are ignoring.

Round(2.5, 0)

CORRECTLY gives 2.0 because (if the value to be rounded is exactly
halfway between the round points) it rounds to the nearest *EVEN*
value. Round(2.499999, 0) gives 2; Round(2.5000001) gives 3. This is
called "Banker's Rounding" and ensures that, on average, the sum or
average of a set of rounded numbers will be close to the sum or
average of the unrounded values. If you always round 0.5 up, then the
rounded values will, on average, be LARGER than the unrounded ones.

Please reread the answers in this thread. This has been explained at
least twice.

 

[Savvoulidis Iordanis]

Wouldn't it be faster if the round function I'd like was supported natively
and not be interpreted for each record I access?
Anyway, I give up. Last John Vinson's explanation was good enough.

One thing to say though, (please no offence, I could be wrong about it) is
that I noticed
a bit aggressive replies from Van T. Dinh (MVP), like he wrote the Access
project and feels very protective about it.
I am a programmer for more than 14 years, using other tools (mainly
PB-Sybase) and started access since last 1 1/2 years, so I use the news for
a long time. I think all newsgroups questions (even those that are not
compehenced from the 1st MVP's answer)
should be respected from those who are dedicated to replying (either on
their own, or accepted by any other company to do it).
No product would be on a high level acceptance as Access is, if users didn't
chat about it (many many times).

Again, thank you all for your responses

 

[Van T. Dinh]

Sorry if it comes through that way but I replied to your original post
answering your direct example and provided the correction to the UDF.
Instead of replying to my post clarifying your question or asking for more
info, you keep asking the same question without explaining "proper"
("proper" means different to different situations as per my later post).

OTOH, if you have used the newsgroups for such a long time, you should have
known the proper process of newsgroup communication.

Access doesn't need my protection! If Access is wrong, I say so. But using
Access incorrectly and then blaming Access is entirely unfair, isn't it?

 

[Savvoulidis Iordanis]

You're OK, friends again...Thanks

 

[Van T. Dinh]

No worries.