rounding

[Guest]

I have a form with a command button which does the following:
[bprice] = [rprice] * 0.77

[rprice] is $12.50 and when you do the above, [bprice] is 9.625

It works fine. When you view the field it shows 9.63 but when you click
into the field, it expands to 9.625. How can I have the actual value be
9.63? What is going on here? I guess rounding.

Thanks.

 

[Guest]

Rounding will do it, but you will not always get what you expect. In the
real world when we round a number, any decimal >= 5 will round the the number
to the left of it up one and < 5 rounds down 1.

VBA doesn't do it quite like that. Depending on the number to the left of
the digit to be rounded off, it may round up and it may round down. The
reason is, over a large set of data, the total will proabably be over stated,
so the rounding algorithm is different.

In this case, 9.625 actually rounds to 9.62
If you can live with that, all that is needed is

[bprice] = Round([rprice] * 0.77, 2)

If you want it to go up to 9.3, you can fool it by using the Format function
which converts it to a string and the CSng function to turn it back to a
single presicion data type or CDbl if that is the field type.

[bprice] = CSng(Format([rprice] * 0.77,"#,##0.00"))