Function needed: Time categorization w/ parameters

[tatarik]

Hi everybody,

I have a database where I put timesheets information.
What I have done is a form where the user only has to input the [Start Time] and the [End Time] of a worker.

From there, I want the form to compute 5 figures:

(1) Normal hours = all daytime hours, for example from 8AM to 5PM
(2) Overtime hours (OT) = all overtime hours, for example from 5PM to 10PM
(3) Night Hours = for example 10PM to 8AM
(4) Sunday Hours = All Sunday Hours, from midnight to midnight.
(5) Holiday Hours​

-Note: while (4) and (5) are relatively straightforward, the first 3 ones seem very hard to me. -

But here is the trick: the definition of Normal/Overtime/Sunday hours isn't exactly the same for every job.
I initially started with If..Then..Else but I ended up with 4x4 conditions which I think is a hassle to redo for every job and different parameters.

Therefore, I want a function that would take in parameters like the defining times for Normal Hours, Overtime Hours and Sunday Hours, a given day Start Time and End Time, and return the actual Normal Hours, Overtime Hours and Sunday Hours.

Is there any way to do it more effectively?

Thanks for the hints

T

 

[pr2-eugin]

Tatarik, you can simplify this by just using If/Else.. All you need to achieve is Create a table that will define what the Normal hours, Overtime and Night hours for each job.. Then you SELECT what job it is and have to use a DLookUp to find the category.. Makes sense?

 

[tatarik]

Hi Paul,

Thanks for the hint!
I made a table as you described, defining the Normal/OverTime hours in a new table, with every record corresponding with a particular contract.

I haven't used DLookup yet so I'll post again if I have additional difficulties.

Take care,

T.

 

[tatarik]

Hi Paul, sorry for the late answer.

I have been implementing the solution you proposed. It's indeed far better organized with an additional table containing the times and normal/overtime hours coefficients.

I am now implementing the Dlookup (which I had never used), so I will keep you updated about the results.

Thanks for your help

 

[pr2-eugin]

Hello tatrik.. Sorry lost track of the thread.. Check out the DLookup Usage Samples, if you get stuck post back.. Good Luck..
http://access.mvps.org/access/general/gen0018.htm