I need help with the Round Function in Access. Is it me or Micros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When you use the Round Function in a query in Access it does not work the
same as the Round Function in Excel.

When you use this function to round numbers such as 2.5, 3.5, 4.5, 5.5 etc.
to the nearest integer it will round the ones with an even number to the left
of the decimal place up and it will roudn the ones with an odd number to the
left of the decimal place down. It works correctly as far as I can see on
any fraction except .5. Someone please tell me that there is no logical
reason for this or please explain the reason for it. I would really
appreciate it. Thank you.
 
http://support.microsoft.com/default.aspx?scid=kb;en-us;225330
OFF2000: New Round Function in Visual Basic for Applications 6.0

When the Round decimal-place argument is zero:
Excel rounds all values of .5 up to the next whole number.

VB rounds odd integers + .5 up
and even integers + .5 down (i.e, 2.5, 4.5, 6.5, etc, would all be rounded
down)

The article tells you how to use Excel's worksheet function within VB code
if you desire the consistency, although not from within a query directly.
 
This is called Banker's Rounding. Because .5 is exactly at the middle, if
..5 is rounded up exclusively (or rounded down exclusively), the rouding will
be biased and the total will not be the same as the true total. In Banker's
Rounding, .5 is rounded to the _nearest_ even (number), e.g. 2.5 to 2 and
3.5 to 4. In theory, this makes the rounding more unbiased and the total of
the rounded values closer to the total of the actual values.

If you don't want to round this way, introduce a tiny bias to tip the exact
..5 one way or another.
 
Thank you for taking the time to help me.

Van T. Dinh said:
This is called Banker's Rounding. Because .5 is exactly at the middle, if
..5 is rounded up exclusively (or rounded down exclusively), the rouding will
be biased and the total will not be the same as the true total. In Banker's
Rounding, .5 is rounded to the _nearest_ even (number), e.g. 2.5 to 2 and
3.5 to 4. In theory, this makes the rounding more unbiased and the total of
the rounded values closer to the total of the actual values.

If you don't want to round this way, introduce a tiny bias to tip the exact
..5 one way or another.
Click to expand...
 
Thank you for taking the time to help me.

George Nicholson said:
http://support.microsoft.com/default.aspx?scid=kb;en-us;225330
OFF2000: New Round Function in Visual Basic for Applications 6.0

When the Round decimal-place argument is zero:
Excel rounds all values of .5 up to the next whole number.

VB rounds odd integers + .5 up
and even integers + .5 down (i.e, 2.5, 4.5, 6.5, etc, would all be rounded
down)

The article tells you how to use Excel's worksheet function within VB code
if you desire the consistency, although not from within a query directly.
Click to expand...
 
I'm also having trouble with Rounding in Access 2002...

Here's my formula:
Extended Cost:([Mileage]*0.345)+[Cost]

When I run that formula through my query, with Cost = $25 and Mileage = 25,
my Extended Cost displays as $33.63, which is correct. However, when I click
on that, the screen removes the $ and displays 33.625 ---

So, I modify the formula to include rounding:
Extended Cost:Round(([Mileage]*0.345)+[Cost],2)

Now, my Extended Cost displays as $33.62, which is NOT correct! When I
click on that figure, the screen removes the $, but RE-displays 33.62 ---

HOW do I get this ROUNDING formula to work? I want the net result to
display AND store 33.63 ---

Any help from anyone out there would be greatly appreciated.
 
Round() uses the Banker's Rounding in which the exact 5 is rounded/removed
and the preceding digit is changed to the nearest even digit. Hence:

Round(33.625, 2) will give 33.62

and

Round(33.635, 2) will give 33.64

If you want to round UP the exact .005, simply introduce a small bias like:

Round(([Mileage]*0.345)+[Cost] + 0.000001, 2)

--
HTH
Van T. Dinh
MVP (Access)



Larry J. said:
I'm also having trouble with Rounding in Access 2002...

Here's my formula:
Extended Cost:([Mileage]*0.345)+[Cost]

When I run that formula through my query, with Cost = $25 and Mileage = 25,
my Extended Cost displays as $33.63, which is correct. However, when I click
on that, the screen removes the $ and displays 33.625 ---

So, I modify the formula to include rounding:
Extended Cost:Round(([Mileage]*0.345)+[Cost],2)

Now, my Extended Cost displays as $33.62, which is NOT correct! When I
click on that figure, the screen removes the $, but RE-displays 33.62 ---

HOW do I get this ROUNDING formula to work? I want the net result to
display AND store 33.63 ---

Any help from anyone out there would be greatly appreciated.

Shannon said:
When you use the Round Function in a query in Access it does not work the
same as the Round Function in Excel.

When you use this function to round numbers such as 2.5, 3.5, 4.5, 5.5 etc.
to the nearest integer it will round the ones with an even number to the left
of the decimal place up and it will roudn the ones with an odd number to the
left of the decimal place down. It works correctly as far as I can see on
any fraction except .5. Someone please tell me that there is no logical
reason for this or please explain the reason for it. I would really
appreciate it. Thank you.
Click to expand...
Click to expand...
 
Thanks, Van... nothing warned me about Banker's Rounding .625 --> .62

FYI... I tried 0.001 for the bias and it worked!


Van T. Dinh said:
Round() uses the Banker's Rounding in which the exact 5 is rounded/removed
and the preceding digit is changed to the nearest even digit. Hence:

Round(33.625, 2) will give 33.62

and

Round(33.635, 2) will give 33.64

If you want to round UP the exact .005, simply introduce a small bias like:

Round(([Mileage]*0.345)+[Cost] + 0.000001, 2)

--
HTH
Van T. Dinh
MVP (Access)



Larry J. said:
I'm also having trouble with Rounding in Access 2002...

Here's my formula:
Extended Cost:([Mileage]*0.345)+[Cost]

When I run that formula through my query, with Cost = $25 and Mileage = 25,
my Extended Cost displays as $33.63, which is correct. However, when I click
on that, the screen removes the $ and displays 33.625 ---

So, I modify the formula to include rounding:
Extended Cost:Round(([Mileage]*0.345)+[Cost],2)

Now, my Extended Cost displays as $33.62, which is NOT correct! When I
click on that figure, the screen removes the $, but RE-displays 33.62 ---

HOW do I get this ROUNDING formula to work? I want the net result to
display AND store 33.63 ---

Any help from anyone out there would be greatly appreciated.

Shannon said:
When you use the Round Function in a query in Access it does not work the
same as the Round Function in Excel.

When you use this function to round numbers such as 2.5, 3.5, 4.5, 5.5 etc.
to the nearest integer it will round the ones with an even number to the left
of the decimal place up and it will roudn the ones with an odd number to the
left of the decimal place down. It works correctly as far as I can see on
any fraction except .5. Someone please tell me that there is no logical
reason for this or please explain the reason for it. I would really
appreciate it. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
I wouldn't use the bias 0.001 in your case since if you try to round with
bias 0.001, says, 33.634, the result is 33.64 which is incorrect. Use the
bias which is _at most_ a tenth of the positional value of the digit you are
going to round, i.e. in your case, you want to round the 3rd decimal place,
the positional value is 0.001 and the bias should then be 0.0001
 
Hey Van,
Interesting. I guess that means

http://support.microsoft.com/default.aspx?scid=kb;en-us;225330
OFF2000: New Round Function in Visual Basic for Applications 6.0

Is very incorrect. It says that bankers rounding only occurs if the decimal
places argument is zero (or omitted) and the decimal component is 0.5 (not
0.25, etc.). Unless I'm reading it wrong...

--
George Nicholson

Remove 'Junk' from return address.


Van T. Dinh said:
Round() uses the Banker's Rounding in which the exact 5 is rounded/removed
and the preceding digit is changed to the nearest even digit. Hence:

Round(33.625, 2) will give 33.62

and

Round(33.635, 2) will give 33.64

If you want to round UP the exact .005, simply introduce a small bias
like:

Round(([Mileage]*0.345)+[Cost] + 0.000001, 2)

--
HTH
Van T. Dinh
MVP (Access)



Larry J. said:
I'm also having trouble with Rounding in Access 2002...

Here's my formula:
Extended Cost:([Mileage]*0.345)+[Cost]

When I run that formula through my query, with Cost = $25 and Mileage = 25,
my Extended Cost displays as $33.63, which is correct. However, when I click
on that, the screen removes the $ and displays 33.625 ---

So, I modify the formula to include rounding:
Extended Cost:Round(([Mileage]*0.345)+[Cost],2)

Now, my Extended Cost displays as $33.62, which is NOT correct! When I
click on that figure, the screen removes the $, but RE-displays 33.62 ---

HOW do I get this ROUNDING formula to work? I want the net result to
display AND store 33.63 ---

Any help from anyone out there would be greatly appreciated.

Shannon said:
When you use the Round Function in a query in Access it does not work the
same as the Round Function in Excel.

When you use this function to round numbers such as 2.5, 3.5, 4.5, 5.5 etc.
to the nearest integer it will round the ones with an even number to
the left
of the decimal place up and it will roudn the ones with an odd number
to the
left of the decimal place down. It works correctly as far as I can see on
any fraction except .5. Someone please tell me that there is no
logical
reason for this or please explain the reason for it. I would really
appreciate it. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
I think the wording in the article is confusing. However, the table
comparing the VBA Round() and Excel Round makes it clear that Bankers'
Rounding is used in the VBA Round().
 
More on this topic of ROUNDING

I'm using an MS template and it doesn't round up like Excel, as this thread explained. It's a Time and Billing db, so when I enter .66 hour (40 minutes), I get $39.60 and not $40 on my reports and invoices.
I think I can solve this by entering an expression on the query field used by the reports and invoices, but for the life of me, I can't figure out how to write it:
Amount: nz([Billable Hours])*nz([Billing Rate]) is the original calculated field.

I've tried all sorts of permutations, they all come back in error. Googled, read through my books and all my class materials.

Any help on this probably really stupid question is appreciated.
Thanks,
Susie
 
Back
Top