FLOOR FUNCTION

[NAVAJAVB]

THE FLOOR FUNCTION WILL NOT BE RECOGNIZED IN MY ACCESS 2000 DB... IT WORKS IN EXCEL...E.G. =FLOOR(183.33,5)=180.
IS THERE A MATHEMATICAL FORMULA I CAN WRITE TO ACHEIVE THE SAME RESULT ??? PLEASE HELP, THANKS VICTOR.

 

[Transcend]

Have you tried Round(expression[, numdecimalplaces])?

Transcend

 

[MichaelRed]

The following is not exactly the same as excel's "floor", but can be used to get the example results. As noted, it was done some time ago, and slightly modified to accomodate this specific post example. This procedure DOES NOT "Shift" a decimal point, but ctually rounds to the specified value and -with the added optional parameter can round the value either UP to the (next) incrental ammount or down to the (previous) incremental ammount.

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
m.red@att.net

Searching for employment in all the wrong places