rounding is ignored in calculated control

[Office User]

I've put a calculated unbound control on report but it seems to be ignoring
the rounding. Here's the formula in Control Source property:
=Round([Calls]/[Total Calls],2)
It's then formatted as Percent with 0 decimals.

If I use =Round([Calls]/[Total Calls],4) then format Percent with 1 or 2
decimals, I can see the number is not rounding. For example, it shows 18.5%
when formatted with 1 decimal using rounding to 4 places, but shows 18% when
formatted with 0 decimals and rounding to 2 places - it should show 19%

The [Calls] field does not contain decimal numbers and is formatted in
original table as Integer. For the report, an aggregate query was used to
totals Calls grouped by Month. [Total Calls] is calculated on report only
(doesn't come from query). DB is Access 2000

Thanks for any help,
Marcia

 

[George Nicholson]

...shows 18% when formatted with 0 decimals and rounding to 2 places - it

should show 19%

Nope, 18% is correct, per its design.

The VB Round function uses banker's rounding:
Round(18.5,0) = 18 (Even numbers + 0.5 are rounded down)
Round(17.5,0) = 18 (Odd numbers + 0.5 are rounded up)
This only applies when the decimal argument is zero and the decimal value is
0.5

Excel's round function behaves differently. For more info:
http://support.microsoft.com/default.aspx?scid=kb;en-us;225330
OFF2000: New Round Function in Visual Basic for Applications 6.0

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I've put a calculated unbound control on report but it seems to be
ignoring
the rounding. Here's the formula in Control Source property:
=Round([Calls]/[Total Calls],2)
It's then formatted as Percent with 0 decimals.

If I use =Round([Calls]/[Total Calls],4) then format Percent with 1 or 2
decimals, I can see the number is not rounding. For example, it shows
18.5%
when formatted with 1 decimal using rounding to 4 places, but shows 18%
when
formatted with 0 decimals and rounding to 2 places - it should show 19%

The [Calls] field does not contain decimal numbers and is formatted in
original table as Integer. For the report, an aggregate query was used to
totals Calls grouped by Month. [Total Calls] is calculated on report only
(doesn't come from query). DB is Access 2000

Thanks for any help,
Marcia

 

[Office User]

Thanks, I didn't know that. I'm so used to Excel I guess. So if I do need
the number rounded "correctly" (meaning > .5 goes to next number) do I need
to use some other function in Access.

Thanks,
Marcia

...shows 18% when formatted with 0 decimals and rounding to 2 places - it
should show 19%

Nope, 18% is correct, per its design.

The VB Round function uses banker's rounding:
Round(18.5,0) = 18 (Even numbers + 0.5 are rounded down)
Round(17.5,0) = 18 (Odd numbers + 0.5 are rounded up)
This only applies when the decimal argument is zero and the decimal value is
0.5

Excel's round function behaves differently. For more info:
http://support.microsoft.com/default.aspx?scid=kb;en-us;225330
OFF2000: New Round Function in Visual Basic for Applications 6.0

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I've put a calculated unbound control on report but it seems to be
ignoring
the rounding. Here's the formula in Control Source property:
=Round([Calls]/[Total Calls],2)
It's then formatted as Percent with 0 decimals.

If I use =Round([Calls]/[Total Calls],4) then format Percent with 1 or 2
decimals, I can see the number is not rounding. For example, it shows
18.5%
when formatted with 1 decimal using rounding to 4 places, but shows 18%
when
formatted with 0 decimals and rounding to 2 places - it should show 19%

The [Calls] field does not contain decimal numbers and is formatted in
original table as Integer. For the report, an aggregate query was used to
totals Calls grouped by Month. [Total Calls] is calculated on report only
(doesn't come from query). DB is Access 2000

Thanks for any help,
Marcia

 

[John Spencer (MVP)]

Just add a very small amount to the value to be rounded so that it does not end
in exactly 5

=Round(([Calls]/[Total Calls]) + .0000001 ,2)

Thanks, I didn't know that. I'm so used to Excel I guess. So if I do need
the number rounded "correctly" (meaning > .5 goes to next number) do I need
to use some other function in Access.

Thanks,
Marcia

...shows 18% when formatted with 0 decimals and rounding to 2 places - it
should show 19%

Nope, 18% is correct, per its design.

The VB Round function uses banker's rounding:
Round(18.5,0) = 18 (Even numbers + 0.5 are rounded down)
Round(17.5,0) = 18 (Odd numbers + 0.5 are rounded up)
This only applies when the decimal argument is zero and the decimal value is
0.5

Excel's round function behaves differently. For more info:
http://support.microsoft.com/default.aspx?scid=kb;en-us;225330
OFF2000: New Round Function in Visual Basic for Applications 6.0

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I've put a calculated unbound control on report but it seems to be
ignoring
the rounding. Here's the formula in Control Source property:
=Round([Calls]/[Total Calls],2)
It's then formatted as Percent with 0 decimals.

If I use =Round([Calls]/[Total Calls],4) then format Percent with 1 or 2
decimals, I can see the number is not rounding. For example, it shows
18.5%
when formatted with 1 decimal using rounding to 4 places, but shows 18%
when
formatted with 0 decimals and rounding to 2 places - it should show 19%

The [Calls] field does not contain decimal numbers and is formatted in
original table as Integer. For the report, an aggregate query was used to
totals Calls grouped by Month. [Total Calls] is calculated on report only
(doesn't come from query). DB is Access 2000

Thanks for any help,
Marcia

 

[Office User]

Thanks, that worked but I had to change the number to .0001. I guess the
other was still too small.

Marcia

Just add a very small amount to the value to be rounded so that it does not end
in exactly 5

=Round(([Calls]/[Total Calls]) + .0000001 ,2)

Thanks, I didn't know that. I'm so used to Excel I guess. So if I do need
the number rounded "correctly" (meaning > .5 goes to next number) do I need
to use some other function in Access.

Thanks,
Marcia

...shows 18% when formatted with 0 decimals and rounding to 2 places - it
should show 19%

Nope, 18% is correct, per its design.

The VB Round function uses banker's rounding:
Round(18.5,0) = 18 (Even numbers + 0.5 are rounded down)
Round(17.5,0) = 18 (Odd numbers + 0.5 are rounded up)
This only applies when the decimal argument is zero and the decimal value is
0.5

Excel's round function behaves differently. For more info:
http://support.microsoft.com/default.aspx?scid=kb;en-us;225330
OFF2000: New Round Function in Visual Basic for Applications 6.0

HTH,
--
George Nicholson

Remove 'Junk' from return address.


I've put a calculated unbound control on report but it seems to be
ignoring
the rounding. Here's the formula in Control Source property:
=Round([Calls]/[Total Calls],2)
It's then formatted as Percent with 0 decimals.

If I use =Round([Calls]/[Total Calls],4) then format Percent with 1 or 2
decimals, I can see the number is not rounding. For example, it shows
18.5%
when formatted with 1 decimal using rounding to 4 places, but shows 18%
when
formatted with 0 decimals and rounding to 2 places - it should show 19%

The [Calls] field does not contain decimal numbers and is formatted in
original table as Integer. For the report, an aggregate query was used to
totals Calls grouped by Month. [Total Calls] is calculated on report only
(doesn't come from query). DB is Access 2000

Thanks for any help,
Marcia