Rounding Issues - HELP!

[Cygnus]

Sorry in advance for the lack of formatting in this posting.

Data:

(column headers)
Net Sales | Royalty Rate | Total Royalty
(data)
4.31 | 50.00% | 2.15
19.35 | 50.00% | 9.68

What gives?

Here is the query that runs this calculation:

UPDATE
[WT_Royalties-detail]
SET
[WT_Royalties-detail].[Total Royalty] = Round([Net Sales]*[Royalty
Rate],2)
WHERE
((([WT_Royalties-detail].Ref)="LSI"));

So why is the first row rounding down while the second row is rounding
up?

Note: Before I added the Round() function, the rows returned 2.155 and
9.675 respectively, if that helps.

Thanks!

[Eman]

"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11*********************@h48g2000cwc.googlegro ups.com...

So why is the first row rounding down while the second row is rounding
up?

That must be the "banker's rounding" issue (not a crime).

You could use something like

Int([Net Sales]*[Royalty Rate]*100)/100

instead of Round(.., 2).

[Cygnus]

Eman wrote:

"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11*********************@h48g2000cwc.googlegro ups.com...

So why is the first row rounding down while the second row is rounding
up?

That must be the "banker's rounding" issue (not a crime).

You could use something like

Int([Net Sales]*[Royalty Rate]*100)/100

instead of Round(.., 2).

Unfortunately, this will not work and should not be used by someone
looking to ROUND.

Here's why.

[Net Sales]*[Royalty Rate]*100 = 2155
Int([Net Sales]*[Royalty Rate]*100) = 215.
Int([Net Sales]*[Royalty Rate]*100)/100 = 2.15

2.155 needs to be rounded to 2.16, not 2.15. All this line of code does
is truncate and that is not what we need to do. We need to round.

Thanks, though.

[Lyle Fairfield]

"Cygnus" <sa*@firstbooks.comwrote in
news:11**********************@c28g2000cwb.googlegr oups.com:

>
Eman wrote:

>"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11*********************@h48g2000cwc.googlegr oups.com...

So why is the first row rounding down while the second row is
rounding up?

That must be the "banker's rounding" issue (not a crime).

You could use something like

Int([Net Sales]*[Royalty Rate]*100)/100

instead of Round(.., 2).

Unfortunately, this will not work and should not be used by someone
looking to ROUND.

Here's why.

[Net Sales]*[Royalty Rate]*100 = 2155
Int([Net Sales]*[Royalty Rate]*100) = 215.
Int([Net Sales]*[Royalty Rate]*100)/100 = 2.15

2.155 needs to be rounded to 2.16, not 2.15. All this line of code
does is truncate and that is not what we need to do. We need to round.

Thanks, though.

There are many VB rounding functions to be found at
http://www.xbeat.net/vbspeed/c_Round.htm
I think all of them will work in VBA.
Perhaps, you can find one that will fit your needs exactly there.

--
Lyle Fairfield

[Eman]

"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11**********************@c28g2000cwb.googlegr oups.com...

>
Eman wrote:

>"Cygnus" <sa*@firstbooks.com???????/???????? ? ???????? ?????????:
news:11*********************@h48g2000cwc.googlegr oups.com...

So why is the first row rounding down while the second row is rounding
up?

That must be the "banker's rounding" issue (not a crime).

You could use something like

Int([Net Sales]*[Royalty Rate]*100)/100

instead of Round(.., 2).

Unfortunately, this will not work and should not be used by someone
looking to ROUND.

Here's why.

[Net Sales]*[Royalty Rate]*100 = 2155
Int([Net Sales]*[Royalty Rate]*100) = 215.
Int([Net Sales]*[Royalty Rate]*100)/100 = 2.15

2.155 needs to be rounded to 2.16, not 2.15. All this line of code does
is truncate and that is not what we need to do. We need to round.

Yeah, i've wrote "something like" ;)
Int(x + 0.5) will provide habitual "arithmetical" rounding.

[Kc-Mass]

I believe the Access (VBA) Round() function rounds every other value ending
in 5 up, then down, up, then down. It seeks to correct the long term
predudice of always rounding the five up.
"Cygnus" <sa*@firstbooks.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...

Sorry in advance for the lack of formatting in this posting.

Data:

(column headers)
Net Sales | Royalty Rate | Total Royalty
(data)
4.31 | 50.00% | 2.15
19.35 | 50.00% | 9.68

What gives?

Here is the query that runs this calculation:

UPDATE
[WT_Royalties-detail]
SET
[WT_Royalties-detail].[Total Royalty] = Round([Net Sales]*[Royalty
Rate],2)
WHERE
((([WT_Royalties-detail].Ref)="LSI"));

So why is the first row rounding down while the second row is rounding
up?

Note: Before I added the Round() function, the rows returned 2.155 and
9.675 respectively, if that helps.

Thanks!