Overtime Reports

[monkeyman77]

Hello All,

I am attempting to create a time-card data base that allows me to pull reports. I have created an example database attached that represents the one I am working on and I was hoping someone could help me with my current issue.

OVERTIME Rules
-Over 40 hours equals OT (no matter what day)
-Double time only occurs when approved (approved with check box in form)


My issue arises when an employee work multiple jobs in one day. I am struggling to create a formula or code that will do the following:

Example:
-If employee work multiple jobs on Tuesday and over 8 hours, the database will add up all of the time for the week, find out if 40 hours have been worked, see that over hours have been work on Tuesday, and apply OT hours to the latest worked job.
-If there are 3 jobs worked in one day and 8 hours is reached during the 2nd job, reg hours and OT hours will have to be added to that job, and the 3rd job will be all OT.


The example database i have created doesn't have my awful attempts at writing this code, it is just an example of how the database is set up.

It would be much appreciated if someone could create a Holiday Miracle for me!!!

-Steven

 

[JHB]

I think for doing it easier for us to understand, make some samples how you want the result, you can set it up in Excel and take a printscreen.

 

[monkeyman77]

Hi JHB,

If you take a look at the example database i have attached and pull the report off of Form1, you'll see the end report i'm looking for. The data in the reg, ot, & DT hours are incorrect because i'm not sure how do to it.

But i'm looking for a way to populate those fields with the following Over time rules:

-Double Time only occurs when the DTapproved check box is checked
-Overtime occurs after 40 hours (regardless of what day, but have to take in to account any time that is considered DT)


Creating the query starts to go over my head when the multiple jobs for one person, during one day comes in to play.

When this happens I need the first 8 hours to be reg and any time after that to be OT, but only if there have been over 40 hours in the week.

Thanks for any help you can provide!
-Steven

 

[JHB]

..The data in the reg, ot, & DT hours are incorrect because i'm not sure how do to it.

Yes I know they are wrong, therefore I asked for a printscreen/Excel sheet where you've the correct values in the correct fields/controls, (the result you want).
So please provide a such overview.

 

[monkeyman77]

Oooo my apologies. I have attached a screen shot. I add some side comments, i hope they make sense.

Thanks again!
-Steven

 

[JHB]

Database attached, try it.
There is a major pitfalls in the way you have set it up, what if working past midnight?
Don't use "Day" and "Name" as field name, they are reserved words.
https://support.office.com/en-us/ar...da237c63eabe?ui=en-US&rs=en-US&ad=US&fromAR=1

 

[monkeyman77]

JHB,

Thank you very much for this. It work really well but I found one error that I am not sure how to fix. If an employee works over 8 hours in a day during one job (example 7am to 10pm), i am getting a run time error 3021. When i go to debug the error the bolded line is highlighted:


If rst![Total] > 8 Then
Set rstBefore = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
& "FROM Employee_Log " _
& "WHERE Time_In< #" & Time_In & "# " _
& "GROUP BY WorkDay, Employee_Name " _
& "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
If rstBefore![Total] > 8 Then
OTHours = rst![Total] - rstBefore![Total]
Else
CalculateHours = (8 - rstBefore![Total])
OTHours = rst![Total] - 8
End If
Else

Any suggestions?

 

[monkeyman77]

Looking closer at this, i think the issue is when there is not record before the record being entered.

 

[JHB]

JHB,

Thank you very much for this. It work really well but I found one error that I am not sure how to fix. If an employee works over 8 hours in a day during one job (example 7am to 10pm), i am getting a run time error 3021. When i go to debug the error the bolded line is highlighted:


If rst![Total] > 8 Then
Set rstBefore = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
& "FROM Employee_Log " _
& "WHERE Time_In< #" & Time_In & "# " _
& "GROUP BY WorkDay, Employee_Name " _
& "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
If rstBefore![Total] > 8 Then
OTHours = rst![Total] - rstBefore![Total]
Else
CalculateHours = (8 - rstBefore![Total])
OTHours = rst![Total] - 8
End If
Else

Any suggestions?

What do you expect the above query should do?
What should happen if no record is returned?

 

[monkeyman77]

Well if there isn't a previous record, that would mean there wasn't any hours yet for that day.

So if no previous record then
Are there more than 8 hours for the current record?
If yes, then OT = total hours - 8 and Reg = 8
If no, then OT = 0 and Reg = total hours

I have attached the database with an added data record. Try running the report for 16-ABC-3.

 

[JHB]

Try it now, else take a printscreen and tell what is wrong and what it should be instead.

 

[monkeyman77]

JHB,

This works great! Last thing I think I need to add is to make the default value zero for all categories.

Example: IF there are only 8 Reg hours then OT and DT hours need to show a value of 0.

Thank you,
Steven

 

[monkeyman77]

I think I figured it out!

Option Compare Database
Dim OTHours As Integer


Function CalculateHours(TheDate As Date, TheName As String, Time_In As Date, DTApproved As Boolean, HourType As String)
  Dim dbs As DAO.Database, rst As DAO.Recordset, rstBefore As DAO.Recordset

  Set dbs = CurrentDb
  
  If HourType = "Reg Hours" Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In<= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
    If rst![Total] > 8 Then
      Set rstBefore = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
      & "FROM Employee_Log " _
      & "WHERE Time_In< #" & Time_In & "# " _
      & "GROUP BY WorkDay, Employee_Name " _
      & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
      If Not rstBefore.EOF Then
      If rstBefore![Total] > 8 Then
        OTHours = rst![Total] - rstBefore![Total]
        CalculateHours = 0
      Else
        CalculateHours = (8 - rstBefore![Total])
        OTHours = rst![Total] - 8
      End If
    Else
        OTHours = rst![Total] - 8
        CalculateHours = rst![Total] - OTHours
      End If
     Else
      OTHours = 0
      CalculateHours = rst![Total]
    End If
  ElseIf HourType = "OT Hours" Then
    CalculateHours = OTHours
  ElseIf HourType = "DT Hours" And DTApproved Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, Sum((DateDiff('n',[Time_In],[Time_Out])/60)-[Lunch]) AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
    CalculateHours = rst![Total]
  
  ElseIf HourType = "DT Hours" Then
    Set rst = dbs.OpenRecordset("SELECT WorkDay, Employee_Name, 0 AS Total " _
    & "FROM Employee_Log " _
    & "WHERE Time_In= #" & Time_In & "# " _
    & "GROUP BY WorkDay, Employee_Name " _
    & "HAVING WorkDay=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Employee_Name='" & TheName & "'")
    CalculateHours = rst![Total]
  
  
  
  End If
End Function

 

[monkeyman77]

Hi JHB,

I've been trying my best to get this to work and have run in to another issue that i can't seem to figure out.

I have added some more data to the database attached.

When i run the report for 16-ABC-3 everything seems to work find. Looking closely at the record on 12/22/16. The OTHours calculate correctly 5pm to 8pm is 3 hours. They are OTHours because Steven has other records on that day that total to 8 hours or more.

But when i run the report for 16-G011, the hours do not calculate correctly.
On 1/3/17 Pete has records for 16-G002 (3pm to 8pm = 5 RegHours) and 16-G011(8Pm to 11pm = 3 RegHours). But for some reason i am receiving 8 RegHours in the report.

Any idea?

Thank you,
Steven

 

[JHB]

Replace the code in the module with the below, see if the result is correct now, (red marked lines are new):

Function CalculateHours(TheDate As Date, TheName As String, TimeIn As Date, DTApproved As Boolean, HourType As String)
  Dim dbs As DAO.Database, rst As DAO.Recordset, rstBefore As DAO.Recordset

  Set dbs = CurrentDb
  If HourType = "Reg Hours" Then
    Set rst = dbs.OpenRecordset("SELECT Day, Name, Sum((DateDiff('n',[TimeIn],IIf([TimeOut]=0,1,[TimeOut]))/60)-[Lunch]) AS Total " _
    & "FROM EmployeeLog " _
    & "WHERE TimeIn<= #" & TimeIn & "# " _
    & "GROUP BY Day, Name " _
    & "HAVING Day=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Name='" & TheName & "'")

    If rst![Total] > 8 Then
      Set rstBefore = dbs.OpenRecordset("SELECT Day, Name, Sum((DateDiff('n',[TimeIn],[TimeOut])/60)-[Lunch]) AS Total " _
      & "FROM EmployeeLog " _
      & "WHERE TimeIn< #" & TimeIn & "# " _
      & "GROUP BY Day, Name " _
      & "HAVING Day=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Name='" & TheName & "'")
      If Not rstBefore.EOF Then
        If rstBefore![Total] > 8 Then
          OTHours = rst![Total] - rstBefore![Total]
        Else
          CalculateHours = (8 - rstBefore![Total])
          OTHours = rst![Total] - 8
        End If
      Else
        OTHours = rst![Total] - 8
        CalculateHours = rst![Total] - OTHours
      End If
    Else
      OTHours = 0
   [B][COLOR=Red]   Set rst = dbs.OpenRecordset("SELECT Day, Name, Sum((DateDiff('n',[TimeIn],[TimeOut])/60)-[Lunch]) AS Total " _
      & "FROM EmployeeLog " _
      & "WHERE TimeIn= #" & TimeIn & "# " _
      & "GROUP BY Day, Name " _
      & "HAVING Day=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Name='" & TheName & "'")[/COLOR][/B]
      CalculateHours = rst![Total]
    End If
  ElseIf HourType = "OT Hours" Then
    CalculateHours = OTHours
  ElseIf HourType = "DT Hours" And DTApproved Then
    Set rst = dbs.OpenRecordset("SELECT Day, Name, Sum((DateDiff('n',[TimeIn],[TimeOut])/60)-[Lunch]) AS Total " _
    & "FROM EmployeeLog " _
    & "WHERE TimeIn= #" & TimeIn & "# " _
    & "GROUP BY Day, Name " _
    & "HAVING Day=#" & Format(TheDate, "mm/dd/yyyy") & "# AND Name='" & TheName & "'")
    CalculateHours = rst![Total]
  End If
End Function

 

[monkeyman77]

JHB,

Thank you very much, that solved the problem!

Earlier you mentioned that the midnight would create and issue. I think i have solved that.

Unfortunately, I have found another problem though. There seems to be an issue calculating overtime to the half hour.

I have attached an updated database with the updates to the code and a picture of the result and how it needs to look.

Any thoughts on how to correct that?

You have been so much help, I do really appreciated it.
-Steven

 

[JHB]

Earlier you mentioned that the midnight would create and issue. I think i have solved that.

Not midnight in itself, but when work past midnight
There is a major pitfalls in the way you have set it up, what if working past midnight?

..
Unfortunately, I have found another problem though. There seems to be an issue calculating overtime to the half hour.
..
Any thoughts on how to correct that?

Change the variable "OTHours" in the top of the module to Double

Dim OTHours As Double