18/07/2020  |   

To simulate these two Excel functions in Access Visual Basic, paste the following code into a new module and save it:

CODE

Public Function Ceiling(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
End Function

Public Function Floor(ByVal X As Double, Optional ByVal Factor As Double = 1) As Double
    ' X is the value you want to round
    ' is the multiple to which you want to round
    Floor = Int(X / Factor) * Factor
End Function

Examples

Ceiling(2.5, 1) equals 3
Ceiling(2.5) equals 3
Ceiling(-2.5, -2) equals -4
Ceiling(-2.5, 2) equals -2
Ceiling(1.5, 0.1) equals 1.5
Ceiling(0.234, 0.01) equals 0.24

Floor(2.5, 1) equals 2
Floor(2.5) equals 2
Floor(-2.5, -2) equals -2
Floor(-2.5, 2) equals -4
Floor(1.5, 0.1) equals 1.5
Floor(0.234, 0.01) equals 0.23