Ceiling function in Access

How to create a Ceiling Function in MS access that behaves the same as t开发者_StackOverflow中文版he one in Excel?

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

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.

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;

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

While this question specifically asked for Access here is the answer for VB.NET

Public Function Ceiling(ByVal value As Double, ByVal factor As Double) As Double
    Return Math.Ceiling(value / factor) * factor
End Function

And the answer in C#

public double Ceiling(double value, double factor)
{
    return Math.Ceiling(value / factor) * factor;
}

I'm posting it here because I needed such a function google sent me to this question but I couldn't find an answer for .Net. I finally figured it out for myself.