Rounding Question

[Bunky]

I have a table that has 3 elements that have been defined as Currency with 2
decimals. Everything worked in the queries perfectly. Now I have been told
they no longer wish to have a decimal at all. I tried changing the
definition to Double with 0 decimals. Then when I run my averaging query, I
still get decimals and rounding. Ideas?

Bunky

 

[Arvin Meyer [MVP]]

In the underlying table change the currency's decimal property from Auto to
0.

 

[Bunky]

Arvin,

I have changed the definition to Double and 0. The table looks great
without any problems. But when I run my averaging query, I get decimals.
Example: I have four values 86, 89, 93, and 94. The average comes to 90.5.
I want it to round to 91. I tried doing the Round function but it does not
always round consistently. What am I doing wrong?

 

[John W. Vinson]

Arvin,

I have changed the definition to Double and 0. The table looks great
without any problems. But when I run my averaging query, I get decimals.
Example: I have four values 86, 89, 93, and 94. The average comes to 90.5.
I want it to round to 91. I tried doing the Round function but it does not
always round consistently. What am I doing wrong?

Using the wrong definition of "consistantly". It *DOES* round consistantly, to
the nearest EVEN number if the fractional part is .5. This is called "banker's
rounding"; it ensures that the average of the rounded values is closer to the
average of the raw values, rather than being smaller as it would be if you
always round down.

What do you want 90.5 to round to (if not 90)? What do you want 91.5 to round
to (it will round to 92)?

John W. Vinson [MVP]

 

[Rick Brandt]

I have a table that has 3 elements that have been defined as Currency
with 2 decimals. Everything worked in the queries perfectly. Now I
have been told they no longer wish to have a decimal at all. I tried
changing the definition to Double with 0 decimals. Then when I run
my averaging query, I still get decimals and rounding. Ideas?

Bunky

The number of decimal places in a Double, Single, or Currency *displayed* can be
influenced by the Format property applied. This does not change how many
decimal places the values actually have however. It only affects what is
painted on the screen.

You could change to either Decimal with zero decimal places defined or use
Integer or LongInteger. Each might have different strategies for dropping the
decimal places though so you would have to see if they suit your needs.

Alternatively you can use expressions to round off all decimal places in all of
your calculations and only show and store the actual rounded result. Then it
wouldn't matter which type you used other than when you missed something and
allowed a fractional value to "get through". Using a type that simply won;t
store fractions would catch that.

 

[Bunky]

Hi John,

in answer to your question - What do you want 90.5 to round to (if not 90)?
This should, and would in normal math rules, round to 91. What do you want
91.5 to round to (it will round to 92)? This would be correct. If the number
is less than 4, it truncates; Greater than 5 rounds up.

Just now I learned that the Mgmt decided to forget the rounding anyway.
Sigh! I did manage to get the results I needed via the Round command.

Thanks for your help!