Ceiling function in Access

Question

Have searched for this, no luck.

Can someone tell me how create a Ceiling Function in MS access that behaves the same as the one in excel?

Answer 1

Looks like there's an answer here (VBA for Access):

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
    ' is the multiple to which you want to round
    Ceiling = (Int(X / Factor) - (X / Factor - Int(X / Factor) > 0)) * Factor
End Function

Answer 2

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

Answer 3

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;

Answer 4

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.