Expression Builder

[Cyd]

Can I use the Expression Builder in Access for performing
ceiling and floor functions.

 

[John Spencer (MVP)]

You can't use the Ceiling and Floor functions as they are Excel functions not
Access functions. (Well, you can, but it's not pretty).

Ceiling:
-Int(-FieldValue)

That works to give the next integer value for positive numbers.
If you want to get the ceiling for other than just the next integer (by 5 or .2
or whatever) then the formula is:

-Int(-FieldValue * (1/Increment))/(1/Increment)

Just substitute your ceiling increment for the increment. So for the next 5

-Int(-FieldValue * 1/5)/(1/5)

for the next .2
-Int(-FieldValue * 1/.2)/(1/.2)

Floor:
-Fix(-FieldValue * (1/Increment)) /(1/Increment)

That works to give the integer value for positive numbers