round off up to ............ 2

[rmh3619]

Hello all,

I have a number field which contains a number which represents an amount of minutes (it's not a time field).

I want this number to be rounded off up to the nearest quarter so if the field contains the number 10 it should be rounded off up to 15 and if the number if 125 it should be rounded off up to 135 etc etc

what is the easiest way to do this?

any help is greatly appreciated!

 

[Golom]

Rounded = iif(nMin mod 15 = 0, nMin, 
              nMin + (15 - nMin Mod 15))

assuming that "nMin" is an integer or long data type.

 

[PHV]

newValue = 15 * ((oldValue + 14) \ 15)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmh3619]

Thank you very much Golum, it works great!

 

[MichaelRed]

While admiting it is somewhat more complex than the previous offerings, the folowing does provide some additional flexibility ...

Public Function basRnd2Val(ValIn As Single, _
                           Rnd2 As Single, _
                           Optional RndDn As Boolean = False) As Single

    'Michael Red    11/27/02    Tek-Tips thread701-414063
    'Modified 1/31/2003 to Include optional Round Down
    'for NAVAJAVB re Tek-Tips thread701-462531

    '? basRnd2Val(183.33, 10, True)
    '180


    '? basRnd2Val(7.49, 3.625)
    ' 10.875

    '? basRnd2Val(7.49, 3)
    ' 9

    '? basRnd2Val(7.49, 0.01)
    '7.5

    Dim ModRmdr As Single
    Dim ValPart() As String
    Dim DecPart() As String
    Dim Shift(2) As Integer
    Dim MyVal As Long
    Dim MyRnd As Long
    Dim MyRtn As Long

    ValPart = Split(Str(ValIn), ".")
    DecPart = Split(Str(Rnd2), ".")

    If (UBound(ValPart) > 0) Then
        Shift(0) = Len(ValPart(1))
     Else
        Shift(0) = 0
    End If

    If (UBound(DecPart) > 0) Then
        Shift(1) = Len(DecPart(1))
     Else
        Shift(1) = 0
    End If

    If (Shift(0) > Shift(1)) Then
        Shift(2) = Shift(0)
     Else
        Shift(2) = Shift(1)
    End If

    MyVal = Val(ValIn) * (10 ^ Shift(2))
    MyRnd = Val(Rnd2) * (10 ^ Shift(2))

    MyRtn = MyVal - (MyVal Mod MyRnd) + MyRnd
    basRnd2Val = MyRtn * 10 ^ (-1 * Shift(2))

    If (RndDn = True) Then
        basRnd2Val = basRnd2Val - Rnd2
    End If

End Function

MichaelRed