By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

Undocumented rounding algorithm

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


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

Nov 13 '05 #2

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

Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

P: n/a
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
Nov 13 '05 #5

P: n/a
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.

Nov 13 '05 #6

P: n/a
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).
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.