Add clock values to report query

[DHookom]

OK, I have tried to figure all this out and have tried some of the solutions without success. Totally my fault. I am going to try and explain again what I need and may find you have already given me the answer at which point I may have to call it a day.

EmployeeID_______EmployeeName________ ClockIn_________________ClockOut______________Hours Worked
__________1_________John Doe__________8/8/2025 8:00AM_________8/8/2025 12:06PM____________4:06
__________1_________John Doe__________8/8/2025 1:00PM__________8/8/2025 5:11PM_____________4:11

I have a query and a form that is working and giving me the above figures but I would like to add the following.

OK, I have employee 1, John Doe. He clocked in for the morning and clocked out for lunch. He clocked back in from lunch and clocked out at end of day. It is easy to look at "Hour Worked" and determine he worked 8 hours and 17 minutes. However, I would like to figure out how to add the, hours worked, 4:06 and 4:11, and show that on the form as the Total Hours Worked. I would like to show this for each employee. Right now, 5 employees to be exact.

For now everyone in the practice will be working basically 8:00AM to 5:30PM, M-F. No overnight, late hours, overtime or weekends.

4:06 and 4:11 are not hours worked. These are both decimal values under the hood that store the amount/part of a day where a day (24 hours) = 1. The value 4:06 is actually stored in your table as 0.170833333333333. Noon has a value of 0.5 while 6:00 AM is 0.25. Your results might be confusing if you expect 4:06 to be greater than 4

 

[Technics]

We understand the requirement. Several approaches suggested.

Post 11 shows two textbox expressions to produce desired results.

On a form, using DSum() expression will result in daily total showing on both lines for each employee.

What exactly did you try and what happened?

I tried to use post 11 but I'm just not sure I know how to use it.

 

[June7]

You build query object that has MinutesWorked and DateWorked fields and use it as form RecordSource.
Then create two textboxes on form and copy/paste expressions.
Or use Ken's custom function.
Then tell us what happens. Tell us more than "I tried".
Or provide db with your efforts.

 

[Gasman]

I tried to use post 11 but I'm just not sure I know how to use it.

You put the expression that @June7 offered as the source of that new control to hold the total.
It will be repeated on each line, but we can work out how to hide the first with Conditional Formatting.

 

[arnelgp]

you create a function (see Module1) on the demo db.
then you call the function within your Query (see Query1).
note the Query is Sorted on EmployeeID and TimeIn.

 

[Gasman]

I would still use CF for hiding the control, as what happens if I have a dental appt at 11:00 and leave at 10:30?

 

[arnelgp]

I don't see anything wrong with your query, but it calculated negative time worked

I only calculate on two records (am and pm punch), as explained in post #1 of the OP.
there will be max of 2 records for each emp per day.

 

[arnelgp]

i changed the code, in case there is only Morning or Evening puch (undertime).

 

[Ken Sheridan]

EmployeeID_______EmployeeName________ ClockIn_________________ClockOut______________Hours Worked
__________1_________John Doe__________8/8/2025 8:00AM_________8/8/2025 12:06PM____________4:06
__________1_________John Doe__________8/8/2025 1:00PM__________8/8/2025 5:11PM_____________4:11

I have a query and a form that is working and giving me the above figures but I would like to add the following.

OK, I have employee 1, John Doe. He clocked in for the morning and clocked out for lunch. He clocked back in from lunch and clocked out at end of day. It is easy to look at "Hour Worked" and determine he worked 8 hours and 17 minutes. However, I would like to figure out how to add the, hours worked, 4:06 and 4:11, and show that on the form as the Total Hours Worked. I would like to show this for each employee. Right now, 5 employees to be exact.

If the Hours Worked column is returning a value of Date/Time data type then you can sum the two values with an expression like this as the ControlSource property of a text box in the form's Detail section:

=DSum("[Hours Worked]","[NameOfYourQuery]", "EmployeeID = " & [EmployeeID] & " And Int(ClockIn) = " & Int([ClockIn]))

If the Hours Worked column contains string expressions, then the expression would be:

=DSum("CDate([Hours Worked])","[NameOfYourQuery]", "EmployeeID = " & [EmployeeID] & " And Int(ClockIn) = " & Int([ClockIn]))

In either case format the text box as "hh:nn"

 

[Gasman]

I only calculate on two records (am and pm punch), as explained in post #1 of the OP.
there will be max of 2 records for each emp per day.

I realise that, but you know most do not think of all the eventualities.

 

[Ken Sheridan]

Why does 12:00:00 AM Midnight get truncated when displayed?

A value of Date/Time data type with a time of day of midnight is an integer, so by default Access formats it as a date. To format it with a zero time of day include the hours, minutes and seconds in the format mask. The ISO standard for date/time notation allows midnight to be formatted in a variety of ways, both as 00:00:00 (start of day) and 24:00:00 (end of day), but Access treats it as the start of the day, e,g,

? Format(Date(),"mm/dd/yyyy hh:nn:ss AM/PM")
08/11/2025 12:00:00 AM

Here in the UK it's also treated as the start of the day, but expressed as zero hours, minutes and seconds:

? Format(Date(),"dd/mm/yyyy hh:nn:ss")
11/08/2025 00:00:00

 

[Ken Sheridan]

I have seen 00:00:00 and 12:00:00 AM, but not 24:00:00. I prefer using miltitary time (24 hour clock) where midnight is 0000, 1:00 PM is 1300, and so on. I find it strange, and undesirable, that by default Access does not display any time when it's 12:00:00 AM, but displays 12:00:01 AM.

We nowadays tend to use the 24 hour clock for most official purposes here in the UK, and I always do in databases, but in everyday use people still use AM and PM. To Access a value at midnight is an integer, but as an integer date/time value is mostly used to record what we think of as a date value, i.e. a value to the precision of one day, it defaults to showing it as a date only. If we are using it as a point in time to a precision of one second, we have to specifically format it as such. The underlying value is exactly the same in each case of course.

Because a date/time value is always to a precision of one second, this can sometimes cause confusion. A common misunderstanding is when using a BETWEEN...AND operation to define a date range. In everyday speech we might say 'between 1st August 2025 and 31st August 2025'. Intuitively we'd take this to include any point of time on 31st August, but to Access only the point of time at midnight at the start of 31st August would be included in the range. This is not a problem if all the dates in rows in a table have a zero time of day, i.e are integers, but if we have a value of 3:00:00 PM on 31st August for instance, that would not be included in the range. A better way of defining a range is on or later than the start date and before the day following the end date. In a query this might be expressed like this:

WHERE TransactionDate >= [Enter start date:] AND TransactionDate < [Enter end date:]+1

rather than:

WHERE TransactionDate BETWEEN [Enter start date:] AND [Enter end date:]

The latter is only completely reliable if we have taken specific steps to disallow dates with a time of day greater than zero in the table definition.

 

[CJ_London]

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

Depends if there is a time element in your data - but it is a fail safe if you are not sure,

You also don’t use quotes around dates in access sql and you don’t need to specify zero time for the start date

I tend to use the >= and < method where the enddate is enddate+1

WHERE TransactionDate >=#2025-08-01# AND TransactionDate<#2025-08-31#+1

 

[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.