Undocumented rounding algorithm

[DaveK]

I am upgrading an MS Access 97 application which uses the format
function to round decimal numbers to two places

e.g. format(dblValue, "standard")

Having done an initial study of this function I can see no obvious
pattern in how it does rounding, it is certainly not bankers rounding.

I need to replicate the rounding algorithm used in the new application
so that the data will be consistent, so if anyone knows the rounding
algorithm used in the Access 97 format function it will save me a lot
of time.

I believe that Microsoft changed the rounding algorithm in Access 2000
so the algorithm I need is the one in Access 97.

[pietlinden]

format does not do any rounding. If you *really* want to round, then
use the ROUND function.

[lylefair]

you can find a bunch of rounding algorithms at
http://www.xbeat.net/vbspeed/c_Round.htm
most are adaptable for Access 97.

[Rick Brandt]

pi********@hotmail.com wrote:

format does not do any rounding. If you *really* want to round, then
use the ROUND function.

Actually Format() does round.

?Format(123.45678,"#.00")
123.46

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rick Brandt]

Rick Brandt wrote:

pi********@hotmail.com wrote:

format does not do any rounding. If you *really* want to round, then
use the ROUND function.

Actually Format() does round.

?Format(123.45678,"#.00")
123.46

Sorry, should have been...

?Format(123.454,"0.0#")
123.45

?Format(123.456,"0.0#")
123.46

It actually performs the "non-bankers rounding" where the 5 always rounds
up.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[DaveK]

The problem is that in Access 97 the 5 does not always round up e.g.

format("3.155","0.00") = 3.15 (down )
format("3.135","0.00") = 3.13 (down)
format("3.335","0.00") = 3.34 (up)
format("3.735","0.00") = 3.73 (down)

These are only a few of the many anomolies to be found. The first digit
after the decimal point seems to have some effect but it is not obvious
what it is.
I am upgrading an Access 97 application to Access 2003 but the new
application has to be able calculate identical amounts as the Access 97
app.

[Steve Jorgensen]

On 10 Jul 2005 01:34:41 -0700, "DaveK" <fo*************@gmail.com> wrote:

The problem is that in Access 97 the 5 does not always round up e.g.

format("3.155","0.00") = 3.15 (down )
format("3.135","0.00") = 3.13 (down)
format("3.335","0.00") = 3.34 (up)
format("3.735","0.00") = 3.73 (down)

These are only a few of the many anomolies to be found. The first digit
after the decimal point seems to have some effect but it is not obvious
what it is.
I am upgrading an Access 97 application to Access 2003 but the new
application has to be able calculate identical amounts as the Access 97
app.

If you're using fixed point numbers (e.g. Currency), the behavior should be
consistent. If you're using Double or Float, then good luck - you'll need it.
Double and Float are approximate types, and anything you do to them is
approximate. Just because a double or float is supposedly equal to, say,
3.155 doesn't mean it is, or that the number can even be precisely represented
as a binary fraction (which is how these numbers are stored).