Add clock values to report query (2 Viewers)

[Ken Sheridan]

So if I want to include any results for entire day of August 31st, my AccessSQL should be?

...
WHERE TransactionDate BETWEEN #8/1/2025# AND #8/31/2025#;

or

...
WHERE TransactionDate BETWEEN "2025-08-01 00:00:00" AND "2025-08-31 23:59:59";

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.

 

[Technics]

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.

 

[Gasman]

You do not say what you actually used?
For the week, it could be more of the same, just with a different criteria for date?

 

[Technics]

You do not say what you actually used?

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.

 

[June7]

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

So instead calculate a field for "first day of week" and aggregate data by that.
Fairly common topic. Review https://www.access-programmers.co.uk/forums/threads/first-date-of-the-week.327105/

 

[Gasman]

Here is some amended ChatGPT code to get your start and end dates for criteria

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

 

[Ken Sheridan]

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:

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:

=TimeElapsed(DSum("CDate(DailyTime)","qryDailyTimeWorked","EmployeeID = " & Nz([EmployeeID],0) & " And WeekStart(1,WorkDate) = #" & Format(WeekStart(1,[WorkDate]),"yyyy-mm-dd") & "#"),"nn")

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.