Rounding to the nearest multiple of 5

[BBOLD]

Is there a way to round a whole number the nearest multiple of 5, ie. 27 rounds to 30 or 12 rounds to 10. I know how to do it in excel but tried it in access with no success. Any help provided will be appreciated.

 

[vbaInet]

You would probably need a function for this:

Public Function GetNearest5(intField as Variant) As Variant
     Dim intRemainder as Long

     If Not IsNumeric(intField) Then
          GetNearest5 = intField
          Exit Function
     End If
     intRemainder = intField mod 5

     GetNearest5 = intField + (5 - intRemainder)
End Function

 

[MarkK]

A generic version ...

Public Function RoundValueToNearest(Value As Single, Nearest As Single) As Single
   RoundValueToNearest = CLng(Value / Nearest) * Nearest
End Function

 

[BBOLD]

Not real familar with queries or access for that matter, so I'm not sure where I would place the code.

 

[John Big Booty]

You'd need to create a VBA module with the the functions that either Lagbolt or vbaInet, presented and then call it in the after update event of your control

 

[John Big Booty]

... or in your query even.