"Round" function not working properly...

[Gary Smith]

Hello--

I have a field value 42.945 which I wish to be treated as
42.95 in a calculation. So naturally I invoke the "round"
function, right? So what do I get? 42.94, not 42.95. It
seems to be truncating, not rounding. What's going on?

Thanks as usual for any assistance that may be forthcoming.

--Gary

 

[Rick Brandt]

Hello--

I have a field value 42.945 which I wish to be treated as
42.95 in a calculation. So naturally I invoke the "round"
function, right? So what do I get? 42.94, not 42.95. It
seems to be truncating, not rounding. What's going on?

Thanks as usual for any assistance that may be forthcoming.

Access uses "Bankers Rounding" where (5)s are rounded towards the nearest
even number instead of always rounding up. The theory being that when
rounding a large column of numbers you round (5)s down half the time and up
half the time (on average) thus avoiding the introduction of an upwards
bias in the final result.

If you want rounding where (5)s always round up you need to write your own
function.

 

[Gary Smith]

What would be the code for the following?

If (3rd digit) > 4 then add 1 to (2nd digit)

Or should I use an entirely different approach? Thanks.

--Gary

 

[Damon Heron]

If you are always sure it will be a thousandths, use
X is your number 42.945
round((X+.001),2)

Damon

 

[Gary Smith]

Damon--

What if the number in question had been, say, 24.954?

Using this formula, initially that would become 24.955.
Then wouldn't that be incorrectly rounded UP? If by
saying "rounding to the nearest even number", Rick is
referring to the 2nd decimal, it seems to me that this
formula would yield 24.96... ???

--Gary

 

[Ernie]

I'm not sure if this will work in access but you could do
something like this:

x = trunc(x + .005, 2) ' x is the number that you need
to round off.

to simulate a simple rounding function.

In this way 49.945 becomes 49.95 and 49.954 becomes 49.95.

Back before there was anything resembling a database, or a
rounding function, that is how we had to do it.

 

[Damon Heron]

You are absolutely correct. With this function, though, it seems to work.
Put this in a module and call with fRound((X+.001),2)
where X is 42.954 and where X is 42.955.....

Function fRound(dblNumber As Double, dblDec As Double) As Double

fRound = (Int(dblNumber * (10 ^ dblDec) + 0.5)) / (10 ^ dblDec)

End Function

Damon

 

[Damon Heron]

Duh...
Leave off the +.001...
as fRound(X,2)
I'm tired.
Damon

 

[Gary Smith]

Ernie--

Maybe I'm doing something wrong, but I can't get it to
work. Perhaps there is no "trunc" function in Access? It
sure seems like a promising idea...

--Gary

 

[Gary Smith]

Damon--

I couldn't get it to work. I'm not familiar with the "^"
symbol, though. What does it do? (I used the Shift-6 key
to get it)

--Gary

 

[Damon Heron]

Do you know the immediate window in VBA screen? type ?fRound(42.945,2) and
press enter. That's assuming you copied the Function "fRound" to a module.
There you can test it and see that it works.
On a form, assuming your number is in a textbox, in the afterupdate event,
or with a command button's click event, put

Me!textboxname =fRound(textboxname, 2)

The function does this:
1) it multiplies your number (42.945) by (ten to the power of 2 (your
second number)) then adds .5 to it and
2) returns the integer - in this case 4295.
3) then it divides the integer 4295 by 10 to the power of 2 to give you the
correct rounded number 42.95
The ^ is the exponential symbol. It is located at Shift 6.

Damon