Some thing wrong in Round function ( Access )

when I used the Round function in Access Query to round number to 2 decimals places

some number Ok

but others not OK

you can see in the following picture

expr2 = [assdsd]*0.5

expr1= round([expr2];2)

I need VBA code to use it to give me a right answers

0.031 , 0.032 , 0.033  ,  0.034 rounded to 0.03

and

0.035 , 0.036 , 0.037  ,  0.038  ,  0.039  rounded to 0.04

|

This is not a bug, it is by intent. Access uses "banker's rounding: values ending in 5 are rounded to the nearest even digit, not to the next higher digit as usual. So 0.235 becomes 0.24 and 0.245 also becomes 0.24 when rounded to 2 decimal places.

See http://allenbrowne.com/round.html for an explanation.

---
Kind regards, HansV
https://www.eileenslounge.com

6 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I need VBA code to use

if you can, please

Ali Elbasry

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

It depends what you want to do.  Here are a bunch of functions, based on a mathematical method originally posted by James Fortune, for rounding to an interval:

 

Public Function RoundUp(dblVal As Double, dblTo As Double) As Double

 

    Dim lngTestValue As Long

    Dim dblTestValue As Double

    Dim dblDenominator As Double

   

    dblDenominator = -1 * dblTo

    dblTestValue = dblVal / dblDenominator

    lngTestValue = Int(dblTestValue)

    RoundUp = -1 * lngTestValue * dblTo

 

End Function

 

Public Function RoundDown(dblVal As Double, dblTo As Double) As Double

 

    Dim lngTestValue As Long

    Dim dblTestValue As Double

    Dim dblDenominator As Double

   

    dblDenominator = dblTo

    dblTestValue = dblVal / dblDenominator

    lngTestValue = Int(dblTestValue)

    RoundDown = lngTestValue * dblTo

 

End Function

 

Public Function RoundToZero(dblVal As Double, dblTo As Double) As Double

 

    Dim intUpDown As Integer

    Dim lngTestValue As Long

    Dim dblTestValue As Double

    Dim dblDenominator As Double

  

    If dblVal < 0 Then

        intUpDown = -1

    Else

        intUpDown = 1

    End If

  

    dblDenominator = intUpDown * dblTo

    dblTestValue = dblVal / dblDenominator

    lngTestValue = Int(dblTestValue)

    RoundToZero = intUpDown * lngTestValue * dblTo

 

End Function

 

Public Function RoundToZero(dblVal As Double, dblTo As Double) As Double

 

    Dim intUpDown As Integer

    Dim lngTestValue As Long

    Dim dblTestValue As Double

    Dim dblDenominator As Double

  

    If dblVal < 0 Then

        intUpDown = -1

    Else

        intUpDown = 1

    End If

  

    dblDenominator = intUpDown * dblTo

    dblTestValue = dblVal / dblDenominator

    lngTestValue = Int(dblTestValue)

    RoundToZero = intUpDown * lngTestValue * dblTo

 

End Function

 

RoundUp rounds towards positive infinity, e.g.

 ? RoundUp(0.235,0.01)

 0.24

? RoundUp(-0.235,0.01)

-0.23

 

RoundDown rounds towards negative infinity, e.g.

 ? RoundDown(0.235,0.01)

 0.23

? RoundDown(-0.235,0.01)

-0.24

 

RoundToZero rounds towards zero:

 ? RoundToZero(0.235,0.01)

 0.23

? RoundToZero(-0.235,0.01)

-0.23

 

RoundFromZero rounds away from zero:

? RoundFromZero(0.235,0.01)

 0.24

? RoundFromZero(-0.235,0.01)

-0.24

 

For more information on rounding algorithms see:

 

http://eetimes.com/design/programmable-logic/4014804/An-introduction-to-different-rounding-algorithms

 

_____________________
Ken Sheridan,
Cannock, England

"Don't write it down until you understand it!" - Richard Feynman

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Microsoft had published a "RoundToNearest" function, which I have used with good results, as it allows you to specify the increment that you want.

https://support.microsoft.com/en-us/kb/209996

-- Finally allowed to use Access at work! So excited! --

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks a lot

I will try all

Ali Elbasry

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

if I want to round to the nearest integer number

1.3  to 1

1.5 to 2

1.6 to 2

etc.

Ali Elbasry

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

if I want to round to the nearest integer number

1.3  to 1

1.5 to 2

1.6 to 2

etc.

This is a far from straightforward question.  As always with rounding algorithms the question of what to do with 'half' values arises.  The different ways of addressing this are outlined in the EE Times article to which I referred you earlier.  The most common approach is to use 'banker's rounding' (round-half-even) which attempts to balance out rounding errors over a set of values.  There are other ways of addressing the problem, however, round-half -up and round-half-down, but then what is meant by 'up' and 'down', which is important in the case of negative numbers.  Mathematicians generally define these as meaning  rounding towards positive infinity, and towards negative infinity respectively, but it they might also be defined as rounding away from zero and towards zero respectively.

So firstly you have to decide which rounding algorithm you wish to use.

That's not the end of the story, however, as you then have to consider to what precision the number to be rounded is expressed.  Values like 1.5, 2.5 etc are precise to one decimal place so when rounding to an integer you just have to decide whether you want to use round-to-even, round-to-odd, round-half-up, round-half-down, or use any of the other methods described in the EE Times article.  But what happens when the value to be rounded is expressed to a precision of more than one decimal place?  Even if the data is expressed to a precision of one decimal place, values resulting from the data, e.g. if it is averaged or multiplied by another value, e.g. a tax rate, expressed to a precision of more than one decimal place, are very likely to be expressed to be expressed to a precision of more than one decimal place, e.g. 1.5 * 0.175 (17.5%, which used to be the UK standard rate of VAT) = 0.2625.  This would result in a gross value of 1.7625, which would of course be rounded to 2 when rounding to an integer.  But what if you get a result of 1.5125.  Do you want to use this as the basis for rounding, or do you want to want to first round it to one decimal place and then round that value.  If so, what does it round to when rounded to one decimal place?  Is it 1.5 or 1.6?  And then which rounding algorithm do you use to round that to an integer?

Access uses round-to-even by default.  If we take the currency data type as an example this is expressed to a precision of four decimal places and usually formatted to two decimal places, e.g. £25.17.  Computations on the value do so on the underlying value with a precision of four decimal places however.  This is to reduce cumulative rounding errors sufficiently for everyday currency calculations.  The result sometimes differs from that which people expect, and can be regarded as erroneous because it differs from that which you'd get if you manually did the calculation in stages and expressed the result at each stage to two decimal places, e.g. when calculating the total tax on a set of items in an invoice.  In fact it is the manual calculation which is incorrect in absolute terms, though not it might not necessarily be so in terms of the business rules adopted in the context in which the calculation is being undertaken.  Access is merely using an algorithm which is acceptable in the majority of business environments, i.e. rounding-half-even of an underlying value expressed to a precision of four decimal places.  If this does not suit your business rules then you need to firstly decide what are the answers to the questions posed in my previous paragraph.  Only then can you write the code to implement the algorithm which suits your purposes.

_____________________
Ken Sheridan,
Cannock, England

"Don't write it down until you understand it!" - Richard Feynman

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated May 11, 2021 Views 3,903 Applies to: