Good Day All, I have a report that shows the hours that are worked by employees, who get paid on a biweekly basis. There is a "Total Hours Worked" column that is further divided into "Week1" and "Week2." It looks like this example:
[HTML]Total Hours Worked
Week1 Week2
8.00
8.00
9.00
9.00
10.00
3.50
6.00
6.00
6.00
6.00
---------------------
44.00 27.50[/HTML]
The reason I have the "Total Hours Worked" column divided into Week1 and Week2 is because I want to be able to calculate whether an employee has worked more than 40 hours per week for Overtime purposes.
The control source for the Week1 work hours is a field named txtWeek1 and here is the code that I use
- =IIF([DateIn]-Forms!frmDates4Pay.txtDateBegin<7,(Format([TimeWorked],"#.00"))," ")
And the control source for the Week2 work hours is a field named txtWeek2 and here is the code that I use
- =IIF([DateIn]-Forms!frmDates4Pay.txtDateBegin>6,(Format([TimeWorked],"#.00"))," ")
The code described above works just as I expect. However, I want to be able to add the columns separately, and my problem is that the fields that I am using to calculate the sum of the time worked is calculating both weeks for each field. I am using a simple Sum Statement for both total fields:
- =Format(Sum([TimeWorked]),"#.00")
Is there a way to sum only those hours under Week1 in a total field separately from the hours in Week2? Those fields are named txtSumWeek1 and txtSumWeek2. As it stands now, both of these fields has the two weeks total displayed. FYI, the user inputs the payperiod begin and end dates from a form named frmDates4Pay and the click event to preview the form filters all days worked to between date begin and date end of the payperiod.
I am running XP and using Access 2002 in 2000 mode.
Any assistance will be greatly appreciated,
David