Rounding Frustration

I have a query that uses the Round function to change decimals into
whole numbers. For 4 years straight the thing has always rounded the
numbers in exactly the same way so that whenever I update my graphs,
the percentages for the historical data are always exactly what they
were the last time I ran the query. Now, for the first time my
percentages are all just slightly off and I have not changed the query.
The only thing I can think of is that it is rounding the numbers
differently. I have not upgraded to a new version of Access or
anything, so why would this be? The data types are all long integer.

re: Rounding Frustration

There have been no changes in the Round() routine that I'm aware of. Paste
the formula(s) you're using into a message so we can see if there is
something that doesn't look right.

--
Wayne Morgan
MS Access MVP


<dhildebrandt@wrha.mb.ca> wrote in message
news:1122730858.692668.225770@g44g2000cwa.googlegr oups.com...[color=blue]
>I have a query that uses the Round function to change decimals into
> whole numbers. For 4 years straight the thing has always rounded the
> numbers in exactly the same way so that whenever I update my graphs,
> the percentages for the historical data are always exactly what they
> were the last time I ran the query. Now, for the first time my
> percentages are all just slightly off and I have not changed the query.
> The only thing I can think of is that it is rounding the numbers
> differently. I have not upgraded to a new version of Access or
> anything, so why would this be? The data types are all long integer.
>[/color]

re: Rounding Frustration

Here is an example of the formula.

Round([SumOfHospital Quality Good]*0.5)

re: Rounding Frustration

Are you aware of the algorithm Access uses for rounding numbers ending in 5
(in the place just past the least significant one)?
The number is rounded to the nearest even number - thus 4.5 rounds to 4,
but 3.5 also rounds to 4.
I don't know whether this is relevant to your case, but it's worth knowing
any time you're rounding.
And as others have stated, it hasn't changed in a long, long time.

Of course if you haven't changed the query, the question arises whether
you've(or someone else has) changed any of the data?
Do you have backup copies of earlier datasets? Do they still return the
same numbers?

<dhildebrandt@wrha.mb.ca> wrote in message
news:1122730858.692668.225770@g44g2000cwa.googlegr oups.com...[color=blue]
> I have a query that uses the Round function to change decimals into
> whole numbers. For 4 years straight the thing has always rounded the
> numbers in exactly the same way so that whenever I update my graphs,
> the percentages for the historical data are always exactly what they
> were the last time I ran the query. Now, for the first time my
> percentages are all just slightly off and I have not changed the query.
> The only thing I can think of is that it is rounding the numbers
> differently. I have not upgraded to a new version of Access or
> anything, so why would this be? The data types are all long integer.
>[/color]

re: Rounding Frustration

I've just recently read that access does random rounding for 5's -
sometime up and sometimes down. If this is correct, it's surprising
that anytime I ran my queries in the past 4 years I've always obtained
exactly the same percentages. You would think that they would vary
from time to time. I have checked the data and it does not appear that
it has changed.

re: Rounding Frustration

deloresh@shaw.ca wrote:[color=blue]
> I've just recently read that access does random rounding for 5's -
> sometime up and sometimes down. If this is correct, it's surprising
> that anytime I ran my queries in the past 4 years I've always obtained
> exactly the same percentages. You would think that they would vary
> from time to time. I have checked the data and it does not appear that
> it has changed.[/color]

The sometimes is not random so you wouldn't see changes from one time to the
next. It just rounds to the nearest even number.

1.5 always rounds to 2
2.5 always rounds to 2
3.5 always rounds to 4
4.5 always rounds to 4
etc..

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

re: Rounding Frustration

So is the pattern... even numbers round up and odd numbers round down?
If so, how would it handle 0.5? I would think it would round up, but I
believe I've seen it round down to 0.

re: Rounding Frustration

That's good to know. If the rounding always follows this pattern, then
something else must have changed. I guess I'll be digging for awhile.
Thanks for the help.

re: Rounding Frustration

I see nothing in the example you've given that should cause a problem. There
could be some corruption in the database or the data. Have you tried a
Compact and Repair? Have you tried the same values in another database file
to see what that gives you?

--
Wayne Morgan
MS Access MVP


<deloresh@shaw.ca> wrote in message
news:1122742005.248794.26990@g43g2000cwa.googlegro ups.com...[color=blue]
> Here is an example of the formula.
>
> Round([SumOfHospital Quality Good]*0.5)
>[/color]

re: Rounding Frustration

Yes I've tried all of that - no answers. Access should round 0.5 to
0, right?

re: Rounding Frustration

If it is exactly 0.5, correct. If it is 0.500001, it will round to 1.
Eventually, if you put enough zeros in there, the decimal to binary
conversion will cause it to round the number to 0.5 before using the Round()
function and it will again round to zero.

--
Wayne Morgan
MS Access MVP


"Delores" <deloresh@shaw.ca> wrote in message
news:1122820909.360349.8440@g43g2000cwa.googlegrou ps.com...[color=blue]
> Yes I've tried all of that - no answers. Access should round 0.5 to
> 0, right?
>[/color]

re: Rounding Frustration

On 30 Jul 2005 09:46:45 -0700, deloresh@shaw.ca wrote:
[color=blue]
>Here is an example of the formula.
>
>Round([SumOfHospital Quality Good]*0.5)[/color]

I don't know why it would suddenly start acting differently than it did
before, but you are aware that, when you multiply floating point there is a
tiny rounding error that can occur in the lower bits depending what your
numbers are. Even what appears to be an integer in your original data might
or might not quite be depending whether you typed it or it was the result of
another calculation.

Try converting both numbers to currency before rounding, and see what happens.

Round(CCur([SumOfHospital Quality Good])*CCur(0.5))

re: Rounding Frustration

Here is an example of the formula.

Round([SumOfHospital Quality Good]*0.5)

re: Rounding Frustration

I am using the round command in a form. This is rounding the amount
good. But when I produce the report using that query the detail lines
are displaying the data correct but the total line has a difference of 2
cents.

How can I fix this?



*** Sent via Developersdex http://www.developersdex.com ***