Rounding Issue (1 Viewer)

[EzGoingKev]

Good morning everyone.

I am using this VBA a google search provided:

Public Function RoundUp(ByVal Num As Double, ByVal Decimals As Integer) As Double
    Dim Factor As Double
    Factor = 10 ^ Decimals
    RoundUp = -Int(-Num * Factor) / Factor
End Function

I am getting inconsistent results when having it round up to two decimal points.

I am using the statement

RoundUp([MyFieldName], 2)

2.849939 rounds up to 2.85. It is working how it is supposed to.

1.97 rounds up to 1.97. This is fine.
1.87 rounds up to 1.88. Not sure why it is doing this.

2.9 rounds up to 2.9. This is fine.
2.2 rounds up to 2.21. Not sure why it is doing this.

Can anyone help with this?

 

[theDBguy]

Not in front of a computer now, but what results do you get if you entered those values in the Immediate Window?

Sent from phone...

 

[EzGoingKev]

Not in front of a computer now, but what results do you get if you entered those values in the Immediate Window?

Sent from phone...

I am not sure what you are asking?

 

[tvanstiphout]

Good morning everyone.

I am using this VBA a google search provided:

Public Function RoundUp(ByVal Num As Double, ByVal Decimals As Integer) As Double
    Dim Factor As Double
    Factor = 10 ^ Decimals
    RoundUp = -Int(-Num * Factor) / Factor
End Function

I am getting inconsistent results when having it round up to two decimal points.

I am using the statement

RoundUp([MyFieldName], 2)

2.849939 rounds up to 2.85. It is working how it is supposed to.

1.97 rounds up to 1.97. This is fine.
1.87 rounds up to 1.88. Not sure why it is doing this.

2.9 rounds up to 2.9. This is fine.
2.2 rounds up to 2.21. Not sure why it is doing this.

Can anyone help with this?

I set a breakpoint on the last line, and then ran Roundup(1.87, 2)
Using the Immediate window (Ctrl+G) I test the intermediate calculations:
?Num, Decimals, Factor
1.87 2 100
?-Num * Factor
-187
?-Int(-Num * Factor)
188
That should give you enough information to debug and fix this yourself. Or you can ask an AI to write the code for you.

 

[Ken Sheridan]

Many years ago I put together the following function with the help of New York mathematician James Fortune:

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

As well as simple rounding to n decimal places, the function allows rounding to any interval, e.g. to simply round up to 2 decimal places:

? RoundUp(1.234,0.01)
1.24

To round up to the nearest interval of 0.05:

? RoundUp(1.234,0.05)
1.25

Over the years I've found that I use it mainly for time values, e.g. to round up to the nearest 5 minute interval:

? CDate(RoundUp(#18:32:41#,#0:05#))
18:35:00

The CDate function is called because otherwise the return value would be expressed as, due to the way in which Access implements the date/time data type, a number representing the fraction of a day:

? RoundUp(#18:32:41#,#0:05#)
0.774305555555555

Alternatively the value can be formatted as date/time:

? Format(RoundUp(#18:32:41#,#0:05#),"hh:nn:ss")
18:35:00

You won't be surprised to learn that there is also a corresponding RoundDown 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

 

[EzGoingKev]

@Ken Sheridan - I am working with (35) numbers.

I used RoundUp([MyFieldName], 0.01)

It did (34) of them correctly.

For some reason it made 2.47 into 2.48.

I checked the cell to see if there was space or anything at the end and there was nothing.

 

[Ken Sheridan]

For some reason it made 2.47 into 2.48.

Strange. It's the Int function which is returning -248 rather than -247 when I step through the code. I would not usually use the function for simple rounding to n decimal places. I designed it originally for rounding to intervals like 0.05 or #0:15#. Try changing it to:

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 = CInt(dblTestValue)
    RoundUp = -1 * lngTestValue * dblTo
 
End Function

Why the CInt function should return a different value from the Int function I've no idea. When I call them in the immediate window both return -247 as expected.

 

[tvanstiphout]

> Why the CInt function should return a different value from the Int function I've no idea
But the documentation does. Check it out.

Type conversion functions (VBA)

VBA language reference

learn.microsoft.com

Int, Fix functions (Visual Basic for Applications)

VBA language reference

learn.microsoft.com

 

[Ken Sheridan]

But the documentation does. Check it out.

I get that. What I can't understand is why when the Int function is used in my function and the operand is an integer number, it returns the wrong value in this one case, but not in the other 34, whereas the CInt funtion returns the correct value. I'm confident the underlying maths is correct, as can be seen in the immediate window:

? -1*0.01
-0.01
? 2.47/-0.01
-247
? Int(-247)
-247
? -1*-247*0.01
2.47

So why does the function return the wrong value when the Int function is used?

? RoundUp(2.47,0.01)
2.48

But the correct value when the CInt function is used

? RoundUp(2.47,0.01)
2.47

I'm sure there is an explanation for this, but it's beyond me I'm afraid.

 

[EzGoingKev]

@Ken Sheridan - the second code set worked. Thanks for your help.

It is weird how the codes worked fine on some numbers and not on others.

 

[xavier.batlle]

I get that. What I can't understand is why when the Int function is used in my function and the operand is an integer number, it returns the wrong value in this one case, but not in the other 34, whereas the CInt funtion returns the correct value. I'm confident the underlying maths is correct, as can be seen in the immediate window:

I think there are a couple of things to take into account in your RoundUp() function :
1) In MS Access VBA, both CInt() and Int() convert values to integers—but they behave quite differently.
2) A double variable in VBA uses binary floating-point (IEEE 754). Many decimal values cannot be represented exactly in binary.
As a result, sometimes an unexpected result occurs with some values
For example if you change the Double variables to Currency variables in your function, it will work as espected using Int() or CInt().

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

    Dim lngTestValue As Long
    Dim dblTestValue As Currency
    Dim dblDenominator As Currency
 
    dblDenominator = -1 * dblTo
    dblTestValue = dblVal / dblDenominator
    lngTestValue = CInt(dblTestValue)
    RoundUp = -1 * lngTestValue * dblTo
 
End Function

 

[arnelgp]

on normal Round() function, the second parameter is an Integer (not a fraction).
so if you are just rounding on 2 decimal places:

' Round Up - always rounds away from zero
Public Function RoundUp(ByVal Value As Double, ByVal DecimalPlaces As Integer) As Double
    Dim factor As Double
    factor = 10 ^ DecimalPlaces
    RoundUp = Int(Value * factor + 0.9999999999) / factor
End Function

' Round Down - always rounds toward zero (truncates)
Public Function RoundDown(ByVal Value As Double, ByVal DecimalPlaces As Integer) As Double
    Dim factor As Double
    factor = 10 ^ DecimalPlaces
    RoundDown = Int(Value * factor) / factor
End Function

example:
?RoundUp(2.47, 2)
?RoundDown(2.47, 2)

resulted both to 2.47