Ceiling function in Access

Question

How to create a Ceiling Function in MS access that behaves the same as the one in Excel?

Solution 1

You can add a Reference to the Microsoft Excel Object Library and use Excel.WorksheetFunction.Ceiling

Solution 2

Since Int() seems to work like Floor(), you can get Ceiling like this: -Int(-x)

Solution 3

This answer uses VBA for Access, and is derived from http://www.tek-tips.com/faqs.cfm?fid=5031:

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

Note that this answer is mathematically correct for negative X. See http://en.wikipedia.org/wiki/Floor_and_ceiling_functions#Spreadsheet_software for background.

Solution 4

Thanks, marg, for the answer. For future reference, here is the VBA function that I wrote after importing the Microsoft Excel Object Library:

Public Function Ceiling(Value As Double, Significance As Double) As Double
    Ceiling = Excel.WorksheetFunction.Ceiling(Value, Significance)
End Function

Then in my query, I was trying to calculate billable hours from actual time worked, rounding up to the next quarter hour:

SELECT Ceiling(([WorkTimes]![EndTime]-[WorkTimes]![BeginTime])*24,0.25) AS BillableTime
FROM WorkTimes;

View more solutions

Comments

[MsBao]

Do I have to make a VBA function that wraps the call to Excel.WorksheetFunction.Ceiling() or can I use that in an expression?

[marg]

You can use it directly in VBA but need to create a wrapper to use the function in a Access Form.

[David-W-Fenton]

I would strongly recommend breaking this down into multiple steps, storing the result of each sub-calculation in a variable with known precision in order to minimize the effects of floating point imprecision.

[Tony L.]

This is such a beautiful, simple solution. I think I would create a function like Arthur did so it's clear in the code what is happening.

[Zev Spitz]

This function doesn't use anything in the Access object model, so why call it VBA for Access?

[emjaySX]

I know this comment is 10 years old, but I think it worth adding that this solution implicitly instantiates an Excel Application object every time it's called, which adds significant overhead. Better to declare a module-level or static variable to instantiate once with code such as static XLapp as Excel.Application: If XLapp is nothing then Set XLapp = new Excel.application and change the calculation line to use XLapp.WorksheetFunction insead of Excel.WorksheetFunction

[MsBao]

"I have no memory of this place…" - Gandalf

[Gustav]

This is a very slow method. See answer from MiloNC how to do this in Access.