In many cases the range will be defined by user entered parameter values, so:
WHERE TransactionDate >= [Enter start date:] AND TransactionDate < [Enter end date:]+1
is better as the user will normally enter the two dates. Generally the parameters will be controls in a form, so you can constrain the values entered to valid dates. Alternatively you can create an auxiliary calendar table, and use a combo boxes for the parameters.
Wow! I want to thank all you guys for the help. I am so glad I found this forum. I actually incorporated, I think, some of what everyone contributed and finally got what I was trying to do to work. I can build custom computers, program and network them, design websites using HTML, CSS, Java and PHP, but when it comes to Access I am very rusty. Last used it back in the 90's. I am so grateful for all the fantastic feedback. Now, to try and figure out how to create a weekly total either in the form or report for each employee. If anyone has any ideas on that please let me know. From what I've seen I know you guys are thinking, piece of cake.
I literally don't know if I can narrow it down. I know I worked with info from June7 and Ken and I changed my calcs and some of my module via arnelgp and BlueSpruce. I was getting my form to give me rounded total hours such as 4:02 hours plus 4:12 hours equals 8 hours but no minutes. Then trying to achieve the same results in my archive form and table I was getting errors. Finally, in making various changes I got everything to work without errors. Right now being so rusty with Access this is my world of trying to make it work, trial and error, but with everyone's help, including yours, it is working.
It is possible to calculate a week number DatePart("ww",Date()) but that won't handle week that crosses years. To see that, output from Immediate Window:
?DatePart("ww",#12/28/2025#)
53
?DatePart("ww",#1/1/2026#)
1
Here is some amended ChatGPT code to get your start and end dates for criteria
Code:
Function StartOfWeek(d As Date) As Date
' vbMonday means week starts on Monday (value 2)
StartOfWeek = d - Weekday(d, vbMonday) + 1
End Function
Function EndOfWeek(d As Date, Optional blnWork As Boolean) As Date
EndOfWeek = StartOfWeek(d) + IIf(blnWork, 4, 7 - 1)
End Function
Sub TestWeek()
Dim today As Date
today = Date
Debug.Print "Today: " & today
Debug.Print "Start of Week: " & StartOfWeek(today)
Debug.Print "End of Week: " & EndOfWeek(today, True)
End Sub
Now, to try and figure out how to create a weekly total either in the form or report for each employee. If anyone has any ideas on that please let me know. From what I've seen I know you guys are thinking, piece of cake.
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.
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.
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.
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.
#Name: Sounds like you need to declare variables, or you misspelled something?... I'm currently busy with something else, but will dig deeper in a while.
How about attaching the latest verion of the accdb you're working on so we can take a look?
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'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.
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!
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.
Will do, they admitted me for further tests and observation, I'm feeling much better. I should've brought my laptop because this iphone safari browser is flaky when editing posts.
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.
