ROUNDING to nearest 500

[Nel222]

Hi there,

Can anyone help me sort out a rounding problem with Access?

I am trying to round a value in a field up or down to the nearest 500.

[PriceNew] * [PercentDep] = [CurrentValue]

I want the current value field to round up or down to the nearest 500. I am using the following script, which works for the most part, except that when the [CurrentValue] is 500, 1500, 2500, etc., it will round it up to the next 1000. At 2500, it is already rounded to the nearest 500, so I don't want it to jump again.

Here is the script I am currently using:

Dim I As Integer
Dim R As Integer
Dim HR As Integer
Dim AD As Integer
Dim Div As Integer
Div = 100
I = Me.CurrentValue
R = (Round(I / (Div * 10), 2))
 
HR = Me.CurrentValue - R * (Div * 10)
 
If (HR / Div) <> 5 Then
 
   If (HR / Div) > 2.5 Then
       AD = 5 * Div
 
   Else
       AD = 0
   End If
 
End If
 
Me.Text32 = (R * (Div * 10)) + AD
End If
 
 
End Sub

Thanks,
Nel

[doulostheou]

Check out the mod function in VBA. It will make coding this much simpler. I found the following description online:

"The division operation gives a result of a number with or without decimal values, which is fine in some circumstances. Sometimes you will want to get the value remaining after a division renders a natural result. The remainder operation is performed with keyword Mod. Its syntax is:

Value1 Mod Value2"

Basically, I would use the int function to determine how many times 500 goes into your number. I would then use the mod function to round up or down. For example:

Function RoundTo500(Num As Integer)
 
Dim rndNum As Integer
Dim newNum As Double
 
rndNum = 500
 
newNum = rndNum * (Int(Num / rndNum))
 
If Num Mod rndNum > rndNum / 2 Then newNum = newNum + rndNum
 
RoundTo500 = newNum
 
End Function
 

[Nel222]

Check out the mod function in VBA. It will make coding ...

Thanks for your response. I haven't tried it yet because I had found another solution that seemed to work. I am posting it here in case you may be interested.

From Tek-Tips Forum:

mugs132 (MIS) 21 Nov 05 14:38
I'm an Accounting Manager at a large steel company. I need the most versatile rounding function I can get.

I found this function and added the "nearest" function so it can now round any way you could possibly want...UP..DOWN..NEAREST. It mimicks MS Excel rounding so you'll never have to explain any differences there.

Example of "rounding up" to the closest nickel or dime in a query: FIELD_NAME: RND_TO_NEAREST([PRICE],.05,"UP")


Example of rounding to the nearest 100 in a query: FIELD_NAME: RND_TO_NEAREST([PRICE],100,"NEAREST")

ENJOY!!!!!!!

Public Function RND_TO_NEAREST(Amt As Variant, Divisor As Variant, DIR_UP_DN_NEAREST As String) As Variant
 
    On Error Resume Next
    Dim Temp As Variant
    Temp = (Amt / Divisor)
    If Int(Temp) = Temp Then
        RND_TO_NEAREST = Amt
        Exit Function
 
    Else
        Select Case UCase(DIR_UP_DN_NEAREST)
            Case "UP"
                Temp = Int(Temp) + 1
                RND_TO_NEAREST = Temp * Divisor
                Exit Function
            Case "DN"
                Temp = Int(Temp)
                RND_TO_NEAREST = Temp * Divisor
                Exit Function
            Case "NEAREST"
                Temp = Round(Amt / Divisor) * Divisor
                RND_TO_NEAREST = Temp
                Exit Function
            Case Else
                Exit Function
        End Select
    End If
End Function

Thanks,
Doug

[ADezii]

Hi there,

Can anyone help me sort out a rounding problem with Access?

I am trying to round a value in a field up or down to the nearest 500.

[PriceNew] * [PercentDep] = [CurrentValue]

I want the current value field to round up or down to the nearest 500. I am using the following script, which works for the most part, except that when the [CurrentValue] is 500, 1500, 2500, etc., it will round it up to the next 1000. At 2500, it is already rounded to the nearest 500, so I don't want it to jump again.

Here is the script I am currently using:

Dim I As Integer
Dim R As Integer
Dim HR As Integer
Dim AD As Integer
Dim Div As Integer
Div = 100
I = Me.CurrentValue
R = (Round(I / (Div * 10), 2))

HR = Me.CurrentValue - R * (Div * 10)

If (HR / Div) <> 5 Then

If (HR / Div) > 2.5 Then
AD = 5 * Div

Else
AD = 0
End If

End If

Me.Text32 = (R * (Div * 10)) + AD
End If


End Sub


Thanks,
Nel

I threw this together along with some sample output before 'going to bed', so you better check it carefully (LOL):

Dim varNumToRound As Variant, intRemainder As Integer
Dim lngNumRoundedTo500 As Long, intIncrement As Integer
 
Dim lngNumOf500s As Long
varNumToRound = InputBox("Enter Number to Round", "Round")
 
lngNumOf500s = Fix(varNumToRound / 500)
intRemainder = varNumToRound Mod 500
 
If intRemainder < 250 Then
  intIncrement = 0
Else
  intIncrement = 500
End If
 
lngNumRoundedTo500 = (lngNumOf500s * 500) + intIncrement
 
Debug.Print "Number: " & varNumToRound & vbCrLf & "Number of 500s: " & lngNumOf500s & vbCrLf & _
            "Remainder: " & intRemainder & vbCrLf & _
            "Number Rounded: " & Format$(lngNumRoundedTo500, "#,#,#,#")

Sample OUTPUTS:

Number: 1500
Number of 500s: 3
Remainder: 0
Number Rounded: 1,500
 
Number: 32456
Number of 500s: 64
Remainder: 456
Number Rounded: 32,500
 
Number: 987654
Number of 500s: 1975
Remainder: 154
Number Rounded: 987,500
 
Number: 97321
Number of 500s: 194
Remainder: 321
Number Rounded: 97,500
 
Number: 497116
Number of 500s: 994
Remainder: 116
Number Rounded: 497,000
 
Number: 1999765332
Number of 500s: 3999530
Remainder: 332
Number Rounded: 1,999,765,500