Public Function CalcTotalHrsMin(ByVal ID As Long, ByVal dte As Date, ByVal QryName As String) As Variant
Dim totalMin As Double
CalcTotalHrsMin = Null
If DCount("1", QryName, "EmployeeID = " & ID & " And DateValue(TimeIn) = #" & DateValue(dte) & "#") = 1 Then
totalMin = DLookup("DateDiff('n',[TimeIn], [TimeOut])", QryName, "EmployeeID = " & ID & " And DateValue(TimeIn) = #" & DateValue(dte) & "#")
CalcTotalHrsMin = (totalMin \ 60) & ":" & Format$(totalMin Mod 60, "00")
Else
If TimeValue(dte) > #12:00:00 PM# Then
With CurrentDb.OpenRecordset(QryName, dbOpenSnapshot, dbReadOnly)
.FindFirst "EmployeeID = " & ID & " And DateValue(TimeIn) = #" & DateValue(dte) & "#"
totalMin = DateDiff("n", !TimeIn, !TimeOut)
.MoveNext
Do Until .EOF
If !employeeID <> ID Or DateValue(!TimeIn) <> DateValue(dte) Then
Exit Do
End If
totalMin = totalMin + DateDiff("n", !TimeIn, !TimeOut)
.MoveNext
Loop
.Close
End With
CalcTotalHrsMin = (totalMin \ 60) & ":" & Format$(totalMin Mod 60, "00")
End If
End If
End Function
Code:
SELECT EmpTable.EmployeeID, EmpTable.EmployeeName, TCTable.TimeIn, TCTable.TimeOut, DateDiff("n",[TimeIn],[TimeOut]) AS MinutesWorked, [MinutesWorked]\60 & ":" & Format([MinutesWorked] Mod 60,"00") AS [Hours Worked], CalcTotalHrsMin([EmpTable].[EmployeeID],[TimeIn],"Query1") AS [Total Hours Worked]
FROM TCTable INNER JOIN EmpTable ON TCTable.EmployeeID = EmpTable.EmployeeID
ORDER BY EmpTable.EmployeeID, TCTable.TimeIn;
BlueSpruce, I have been trying to use this as a guideline for creating a weekly total for each employee but haven't been successful. Do you have any ideas on this.
Yes, I try to use everything sent but my problem is still how to implement it. I tried what Ken posted but I keep getting, if I can remember, a #Name? error. I googled it and tried making some changes but didn't help. Got rid of the error but still no solution. As I said, all this is like starting over. What I would like to do, only having five employees, is list each employee with total hours worked for the week either at the footer of my form or in a report. It seems easy when I think about it having the days hours worked total already but it just gets confusing. I am working on trying to create a group report but not to successful. Sorry I can't be more precise on the errors.
My TimeArithmetic demo contains the following function to return the 'week starting' date for any date:
Code:
Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant)
' Returns 'week starting' date for any date
' Arguments:
' 1. intStartDay - weekday on which week starts, 1-7 (Sun - Sat)
' 2. vardate - optional date value for which week starting
' date to be returned. Defaults to current date
If IsMissing(varDate) Then varDate = VBA.Date
If Not IsNull(varDate) Then
WeekStart = DateValue(varDate) - Weekday(varDate, intStartDay) + 1
End If
End Function
In my demo the total time worked for the current week by an employee could be returned in a text box in the form by calling the following expression, which is a modification of that I posted earlier for returning the total daily time worked per employee:
This returns the time worked in the week in the format 'hh:nn', and caters for times in excess of 24 hours. In the amended expression,the DSum function is called rather than the DLookup function, and as a consequence, the DailyTime values from the query need to be converted to DateTime data type by calling the CDate function. The WeekStart function is used to restrict the result to the week in which the WorkDate value returned by the query falls.
Thanks Ken for the response on this but having issues with implementing how should work. Got an error that it didn't recognize TimeElapsed, had to add my table to EmployeeID but still getting syntax errors.
I'm heading to the hospital emergency room, I had surgery early this morning and feeling severe dehydration, most likely from prolonged exposure to propofol anesthesia. I will try to follow this on my phone...
I hope everything is OK. I know how that can feel because I went into severe dehydration after cutting my grass in 97 degree NC weather and had to be rushed to the emergency room. I drank ice cold water trying to cool off which put my body into shock and made me sick as a dog. Never knew that was a no no when you are severely dehydrated.
Thanks for the well wishes. 102.6 fever, blood lactates, creatine phospho way high. Dark urine, concern for rhabdomyolisis kidney damage. This is not fun at all. They have me on double saline IV's to rehydrate me. Can't chat more, hope to recover soon, thanks again for your thoughts. Hopefully others here in AWF will chime in soon and fix your accdb. Cheers!
open ReportParam form and select which employee and start of week to print (report).
select start of the week based on what records you have on your table.
Thanks Ken for the response on this but having issues with implementing how should work. Got an error that it didn't recognize TimeElapsed, had to add my table to EmployeeID but still getting syntax errors.
If you try the expression in my demo, which I attached to post #6, you should see how it works. Hopefully you'd then be able to reproduce it in your file. To use the functions just copy and paste the entire basDateTimeStuff module into your file.
I think I have everything in the database that the client will need. I really appreciate everyone's help. Greatest forum ever. If not for you guys I would have pulled out the little hair I have left. If it is OK with everyone I have added the form in the photo that will be opened by using the Credits button (marked) to the database, because I sure didn't do it all on my own.
I think I have everything in the database that the client will need. I really appreciate everyone's help. Greatest forum ever. If not for you guys I would have pulled out the little hair I have left. If it is OK with everyone I have added the form in the photo that will be opened by using the Credits button (marked) to the database, because I sure didn't do it all on my own.
