Ceiling or Roundup function 1

[ReeferEd]

I need to create a report that bills out a mechanic’s time for performing quick temperature checks on refrigerated cargo. The customer is billed at a flat rate for checking forty pieces of equipment per hour; any fraction of time is rounded up to the next quarter hour. Forty checks would be one hour; forty-one rounds up to one and one quarter hour.
I’ve been using the ceiling function in Excel but it doesn’t seem to be available in Access.
Any suggestions would be very much appreciated
Thanks, Reefer Ed

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmmmmmmmmmm,

Reaching back, I found this from long aeay and far ago. It is NOT specific to your situation, but should do nicely.

Public Function basRound(AmtIn As Currency, AmtRnd As Single) As Currency

    'Michael Red 11/20/2000 To Round UP [AmtIn] to the Next Highest [AmtRnd]

    Dim ModRmdr As Single

    If (AmtIn >= AmtRnd) Then
        ModRmdr = (AmtIn * 100) Mod (AmtRnd * 100)
        If (ModRmdr <> 0) Then
            basRound = AmtIn + AmtRnd - (ModRmdr / 100)
         Else
            basRound = AmtIn
        End If
     Else
        basRound = AmtIn
    End If

End Function

Usage for your app would be ~~~~~:

? basRound(43, 10)
50
? basRound(41, 10)
50
? basRound(40, 10)
40
? basRound(39, 10)
40

So, for the # of units serviced, this function would return the 'rounded UP' number of units to bill for. The remainder of your calc. is just to divide the results by the #Units per hour (40) and Multiply by hte hourly rate.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ReeferEd]

Thank you for your quick response and for taking the time to answer my question. I’ll give this a try. Thanks again.
Reefer Ed

 

[Dan8376]

If you use a function in Excel then you can use that same function in Access, you just have to do a few simple steps.
[ul][li]Install the Microsoft Excel 9.0 Object Library (note your number might not be 9.0, it just depends on your version of Access) If you don't know how to install a library go to your Microsoft Visual Basic window (ALT - F11), then goto Tools, References.
[/li] [li]Next create a module or use an existing module that you store your functions in.[/li] [li]Create whatever functions that you want to use from Excel, for example the RoundUp function from Excel would be created like this:

Function RoundUp(arg1 As Double, arg2 As Double) As Double
RoundUp = Excel.WorksheetFunction.RoundUp(arg1, arg2)
End Function

[/li] [li]Now you should be albe to call this function from your query.[/li][/ul]
Hope this helps
Dan

 

[ReeferEd]

Thanks Dan. This tip will also help in other areas of my project.
Reefer Ed