round vs. formatnumber

[jvv]

I have problems with the round function:
1. Round(ExchangeRate,2) gives 54.5099983 when ExchangeRate = 54.51
2. Banker's rounding. Ex. round(2.725) gives 2.72

The workaround I use for the first problem is to introduce a constant in the
expression. Example: Round(ExchangeRate*1,2) = 54.51
Why doesn't microsoft fix this problem?

However the 2nd problem is more troublesome because we use Excel's Round
behaviour as a "standard".

Because of this, I don't want to use the Round function anymore. I found an
alternative by using the FormatNumber function. However, every now and then,
I find undocumentated behaviour of the FormatNumber function, such as:
1. The result of FormatNumber(ExchangeRate,2) = 54.51 is a text, not a
number. To make it a number, I use: FormatNumber(ExchangeRate,2)*1 = 54.51
2. If the query using FormatNumber is right joined with a table which
contains records that is supposed to give null values. The result of the new
query is #Error. So I have to trap the null values in the bottom query.

I'm afraid I will be encountering more undocumented behaviours of
FormatNumber in the future. Could anyone please explain the other
undocumented behaviours of FormatNumber.

 

[Allen Browne]

Problem 1:
Round() returns a Variant.
Typecast the result to Currency.

Currency can't be null, so use:
CCur(Nz(Round([ExchangeRate],2),0))

Problem 2:
Use a custom rounding function where you can turn banker's rounding off.
Here's one by Ken Getz:
http://www.mvps.org/access/modules/mdl0054.htm