Good Day All, I have a report that prints the time worked during the biweekly payperiod for the staff. The report distinguishes between the first and second weeks of the payperiod. There are 21 different pay/nonpay categories in the payroll. For example - Worked, Sick, Vacation, Holiday are examples of employees receiving pay for the day; however, No-Sick, LWOP, Tardy, No-Call/No-Show are examples of leave types where no pay was received.
My challenge is two-fold: I want to be able to sum separately the total of hours paid for Week1 and Week2, and to sum only hours for which the employee will be paid, but I still want the non-paid hours to be reflected in the report. The following is a rough sketch of how the report is laid out:
Total Hours Worked
Here is a copy of the query:
SELECT tblMasterEmployee.LastName, tblMasterEmployee.FirstName, tblShiftsTimeWorked.WorkShiftHoursWorkedID, tblShiftsTimeWorked.DateIn, tblShiftsTimeWorked.EmployeeID, tblShiftsTimeWorked.TimeTypeID, Format(tblShiftsTimeWorked.TimeOut-tblShiftsTimeWorked.TimeIn,"Short Time") AS [Time], tblShiftsTimeWorked.TimeIn, tblShiftsTimeWorked.TimeOut, DateDiff("n",tblShiftsTimeWorked.TimeIn,tblShiftsTimeWorked.TimeOut) AS Minutes, IIf(tblShiftsTimeWorked.TimeOut<tblShiftsTimeWorked.TimeIn,[Minutes]+1440,[Minutes]) AS Minutes2, tblShiftsEmployees.LunchMin, tblShiftsTimeWorked.LunchMin, ([Minutes2]-tblShiftsTimeWorked.LunchMin)/60 AS TimeWorked, tblShiftsTimeWorked.Notes, tblLeaveType.LeaveType, IIf([DateIn]-Forms!frmDates4Pay.txtDateBegin<7,(Format([TimeWorked],"#.00"))," ") AS Week1, IIf([DateIn]-Forms!frmDates4Pay.txtDateBegin>6,(Format([TimeWorked],"#.00"))," ") AS Week2
FROM tblLeaveType INNER JOIN (tblShiftsEmployees RIGHT JOIN (tblMasterEmployee INNER JOIN tblShiftsTimeWorked ON tblMasterEmployee.EmployeeID = tblShiftsTimeWorked.EmployeeID) ON tblShiftsEmployees.[WorkShift-EmployeeID] = tblShiftsTimeWorked.WorkShiftEmployeeID) ON tblLeaveType.LeaveTypeID = tblShiftsTimeWorked.TimeTypeID
ORDER BY tblMasterEmployee.LastName, tblMasterEmployee.FirstName;
As it is now, all I am able to do is add all of the hours for both weeks into one of the text boxes. I want Week1's hours to sum in txtSumWeek1 and I want Week2's hours to sum in txtSumWeek2. Also I only want hours that are payable to sum, although I want both paid-hours and non-paid-hours to show in the report. I tried the following line which was suggested in an earlier post
but I received an error that the expression was too complex. I tried that line in both the query and the report.
Any assistance will be greatly appreciated. FYI, I am running XP and using Access 2002 in 2000 mode.
Thanks in advance,