Results 1 to 5 of 5

Thread: IIF help

  1. #1
    Join Date
    May 2012

    Unanswered: IIF help

    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. #2
    Join Date
    Jan 2003
    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. #3
    Join Date
    Apr 2004
    metro Detroit
    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. #4
    Join Date
    May 2012
    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.

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    personally I think you'd be better off using a table to define the rate, rather than IIF's 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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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