marcnz

May 10 2011, 03:11 PM

Hi,

He have to calculate values at 3 decimal places in a control set Fixed 3 decimal. But we type in sometimes number with 4 decimals and we expect Access to round it accurately. any trailing 5 and more should round up.

I have set a message box to see exactly what happens for some numbers input and I have attached the screenshot.

HAs you can see:

0.0205 rounds to 0.021 which is expected

6.5915 rounds to 6.591 which is not expected.

Interestingly, the value of 6.5915 is actually considered as 6.4614998......! thus, considering it lower than 6.5915 as typed in!

Anybody has a fix for this?

Thanks.

Jeff B.

May 10 2011, 03:18 PM

Search on-line for "Banker's Rounding". MS Access and MS Excel do NOT use the same rounding rule.

The "problem" with always rounding up on "5" or more is that 1,2,3,4 rounds down, and 5,6,7,8,9 round up. Notice that there's one more digit in the "up" rounding?!?

Osuspect what you're seeing is a variation on the "5-up" rule that says that even-number digits preceeding the "5" cause the round to go up, while odd-number digits preceeding the "5" cause a 'round-down'. That should, theoretically, end up with a more even distribution of rounding.

Just try explaining that to everyone in the world, who learned the "5-up" rounding rule in school!

MikeLyons

May 10 2011, 03:25 PM

Computers just don't do fractions that well... there is always going to be some amount of rounding error if you do deep enough into decimal places.

ome people like to use the Currency data type as it is really not a floating point type at all but rather a "scaled integer" type and supports up to four "decimal places" through division by factors of ten.

Mike

pere_de_chipstick

May 10 2011, 03:40 PM

Hi marcnz

Back Leach suggested:

FormatNumber(Number, NumDigitsAfterDecimal)

in

this post, he added a caveat later in the thread, but might be worth looking at.

hth

rbianco

May 10 2011, 04:58 PM

In addition to what has already been pointed out:

you state:

Well, according to your message box it is not 6.5915 that is rounding down - it is 6.59149980545044, and that value is supposed to round down to 6.591

Just because 6.59149 rounds up to 6.5915, does not mean you continue to pass up the rounding.

In 6.59149 the last digit of "nine" does increment the "four" to a "five", but you must always consider the original value of all the digits. So, when rounding 6.59149980545044 to 3 decimal places you then take the 4 original decimal places of 6.5914 - and that rounds to 6.591. You do not take the already rounded value of 6.5915, and further round that up to 6.592.

Gustav

May 11 2011, 10:33 AM

> 6.5915 is actually considered as 6.4614998

on't know exactly what you mean but it has nothing to do with your format but is caused by your code for the calculation.

You will have to reveal this together with some example input (typed) and the desired output.

/gustav

rbianco

May 11 2011, 01:45 PM

If you look at the picture attached to the OP the message box clearly reveals that the value being rounded is "6.59149980545044", yet in the post it is expressed as "6.5915". While "6.5915" should (and does) round to "6.592", "6.59149980545044" does not. Unless a custom function is applied to the decimal portion, the value of "6.59149980545044" rounds to "6.591", just has it should.

Is to if the value truely is "6.5915", yet somehow the system "sees" it as "6.59149980545044" (as Gustav's observation "but is caused by your code for the calculation" implies), I cannot speak. I do know that if I store the value but 6.5915 in a table as a double to 5 decimal places, and then put this value into a query as Round("6.5915",3) I get 6.592.

This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please

click here.