Rounding Issue (3 Viewers)

E

EzGoingKev

Registered User.
Local time
Today, 06:10
Joined
Nov 8, 2019
Messages
205
Good morning everyone.

I am using this VBA a google search provided:

Code: 
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
Code: 
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?
 
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...
 
theDBguy said:
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...
Click to expand...
I am not sure what you are asking?
 
EzGoingKev said:
Good morning everyone.

I am using this VBA a google search provided:

Code: 
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
Code: 
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?
Click to expand...
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.
 
Last edited:
Many years ago I put together the following function with the help of New York mathematician James Fortune:

Code: 
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:

Code: 
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
 
@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.
 
EzGoingKev said:
For some reason it made 2.47 into 2.48.
Click to expand...

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:

Code: 
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 said:
But the documentation does. Check it out.
Click to expand...

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.
 
Ken Sheridan said:
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:
Click to expand...
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:
? Int(-1.0#*-247.0#/0.01# )
24699

? CInt(-1.0#*-247.0#/0.01# )
24700
If you change the Double variables to Currency variables in your function, it will work as espected using Int() or CInt().

Code: 
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

I learnt the hard way that using doubles when rounding not always works as expected.
 
Last edited:
on normal Round() function, the second parameter is an Integer (not a fraction).
so if you are just rounding on 2 decimal places:
Code: 
' 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
 
It is because you are using Double. Apply Decimal:

Code: 
Value = 2.849939
? RoundUp(Value, 2)
 2.85

Value = 1.97
? RoundUp(Value, 2)
 1.97

Value = 1.87
? RoundUp(Value, 2)
 1.87
 
Value = 2.9
? RoundUp(Value, 2)
 2.9

Value = 2.2
? RoundUp(Value, 2)
 2.2

The function:

Code: 
' Rounds Value up with count of decimals as specified with parameter NumDigitsAfterDecimal.
'
' Rounds to integer if NumDigitsAfterDecimal is zero.
'
' Optionally, rounds negative values away from zero.
'
' Uses CDec() to prevent bit errors of reals.
'
' Execution time is about 0.5µs for rounding to integer,
' else about 1µs.
'
' 2018-02-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RoundUp( _
    ByVal Value As Variant, _
    Optional ByVal NumDigitsAfterDecimal As Long, _
    Optional ByVal RoundingAwayFromZero As Boolean) _
    As Variant

    Dim Scaling     As Variant
    Dim ScaledValue As Variant
    Dim ReturnValue As Variant
    
    ' Only round if Value is numeric and ReturnValue can be different from zero.
    If Not IsNumeric(Value) Then
        ' Nothing to do.
        ReturnValue = Null
    ElseIf Value = 0 Then
        ' Nothing to round.
        ' Return Value as is.
        ReturnValue = Value
    Else
        If NumDigitsAfterDecimal <> 0 Then
            Scaling = CDec(Base10 ^ NumDigitsAfterDecimal)
        Else
            Scaling = 1
        End If
        If Scaling = 0 Then
            ' A very large value for NumDigitsAfterDecimal has minimized scaling.
            ' Return Value as is.
            ReturnValue = Value
        ElseIf RoundingAwayFromZero = False Or Value > 0 Then
            ' Round numeric value up.
            If Scaling = 1 Then
                ' Integer rounding.
                ReturnValue = -Int(-Value)
            Else
                ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
                On Error Resume Next
                ScaledValue = -Int(CDec(-Value) * Scaling)
                ReturnValue = ScaledValue / Scaling
                If Err.Number <> 0 Then
                    ' Decimal overflow.
                    ' Round Value without conversion to Decimal.
                    ScaledValue = -Int(-Value * Scaling)
                    ReturnValue = ScaledValue / Scaling
                End If
            End If
        Else
            ' Round absolute value up.
            If Scaling = 1 Then
                ' Integer rounding.
                ReturnValue = Int(Value)
            Else
                ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
                On Error Resume Next
                ScaledValue = Int(CDec(Value) * Scaling)
                ReturnValue = ScaledValue / Scaling
                If Err.Number <> 0 Then
                    ' Decimal overflow.
                    ' Round Value without conversion to Decimal.
                    ScaledValue = Int(Value * Scaling)
                    ReturnValue = ScaledValue / Scaling
                End If
            End If
        End If
        If Err.Number <> 0 Then
            ' Rounding failed because values are near one of the boundaries of type Double.
            ' Return value as is.
            ReturnValue = Value
        End If
    End If
    
    RoundUp = ReturnValue

End Function

I can't post links to my repositories but a file is attached to my collection of rounding functions.
 

Attachments

All the above shows is that my choice of using the Decimal datatype when doing this sort of arithmetic saves time and effort (and frustration).

As I have said many time on Access World, If there is one thing that VBA truly needs it is a native decimal type rather than having to use the Variant decimal data type.
 
You must log in or register to reply here.

Similar threads

F
Word MailMerge OpenDataSource - password protected ACCDE
Replies
14
Views
1,350
cheekybuddha
cheekybuddha
S
End of the Month
Replies
4
Views
824
Sebsboutique
S
T
Round function being ignored in query
Replies
11
Views
1,160
The_Doc_Man
The_Doc_Man
S
Format number with condition
Replies
3
Views
655
CJ_London
C
N
Solved How get lid of domain function and replace them with opening recordset in MS Access VBA
Replies
10
Views
901
nector
N

Users who are viewing this thread

Total: 4 (members: 2, guests: 2)
Back
Top Bottom