1. Registered User
Join Date
May 2012
Posts
89

I fave a continues form that calculates cost per hour for external contractors that we hire.
It may sometimes happen that they need to work outside office hours.
For these hours the wage is higher.
But sometimes they start in office hours and work a longer period that day even outside office hours.
I want that these "officehours" and "Outside Officehours" be calculated.

Now i have this:
=IIf(([StartTime] Between #18:00:00# And #00:00:00#),[EveningWage],"")
=IIf(([StartTime] Between #00:00:00# And #07:00:00#),[NightWage],"")
=IIf(([StartTime] Between #07:00:00# And #18:00:00#),[DayWage],"")

Ofcourse now the start time needs to be in the certain wage "textbox" to be calculated the right way.

I hope someone can help me with this.
Or point me the right way.
Last edited by Missinglinq; 01-02-15 at 09:15.

2. Registered User
Join Date
Jan 2003
Location
Minneapolis
Posts
59
Question: If a contactor worked from, say, 14:00:00 to 20:00:00, would they be paid the DayWage for 4 hours and EveningWage for the rest, or is their wage solely determined by their StartTime?

3. Registered User
Join Date
Apr 2004
Location
metro Detroit
Posts
634
You need to check the end time as well as the start time. 'Between' includes both parameters, so you'll also need to get rid of your overlap. Assuming you have a field named EndTime

=IIf(([StartTime] Between #00:00:00# And #06:59:59#) and ([EndTime] Between #00:00:00# And #06:59:59#),[NightWage],"")
=IIf(([StartTime] Between #07:00:00# And #17:59:59#) and ([EndTime] Between #07:00:00# And #17:59:59#),[DayWage],"")
=IIf(([StartTime] Between #18:00:00# And #12:59:59#) and ([EndTime] Between #18:00:00# And #12:59:59#),[EveningWage],"")

For the worked hours that cross shifts, you'll need to calculate how many hours are in each shift. I'm not sure exactly how/where you're using the results in your form, but you would check the shift for both the start time and end time. If the shifts are the same, the total hours would be binned to that shift. If not, you would would use DateDiff to find the number of hours between the start time and the end of the start shift then use DateDiff again to find the number of hours between the start of the end shift and the end time.

4. Registered User
Join Date
May 2012
Posts
89
There is indeed the issue...
Sometimes it happens that the StartTime is 23:00 and the EindTime is 08:00.
And so there are 6 NightWage hours and 1 EveningWage and 2 DayWage.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
personally I think you'd be better off using a table to define the rate, rather than IIF's
..an advantage of the table approach is that you can handle special cases (eg holiday working) and so on. eg one Saturday might overtime, another Saturday could be overtime *1.5 and so on.
../the main reason is that a table rules based approach allows trhe user to do whatever the heck they like when they like, its under their control. so there is none of the last minute shouting and screaming from the user that they need a change right now, and transfer the reason fro the incipient failure from their shoulders onto your broad shoulders. compound IIF's can be a pig to debug, sometimes tricky to locate. where as a query is much easier to maintain and extend over time. depending on how complex your business rules are you may need to implement that query as a stored procedure or VB function. but encapsulating the business rules into VBA is far better than compound IIF's

it would probably need to be a rules table(s)
one thing I do remember form years ago is that there are lots of things you can screw up in table design, but wages is definitely NOT one of them

one thing to consider is thart after defining what he applicable rate is for a job it should be stored against that, so changing the rules at a later date doesn#t affect historic calcuatlions

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•