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

 

[BlueSpruce]

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.

If you post your accdb we can better help you.

 

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

 

[BlueSpruce]

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.View attachment 121003

Hi Arnel, I downloaded your demo and added a tc record that starts on 8/7 and ends on 8/8.
I don't see anything wrong with your query, but it calculated negative time worked

 

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

 

[BlueSpruce]

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

Now it calculates correctly. I don't know why it didn't before

You're not taking into account the seconds?

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

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;

 

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

 

[BlueSpruce]

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

 

[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

 

[BlueSpruce]

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

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. SQL Server, Oracle, and other db servers display 12:00:00 AM with default DATETIME datatypes.

 

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

 

[BlueSpruce]

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.

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";

 

[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

 

[BlueSpruce]

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

It's a habit from working for so long with DATETIME YEAR TO SECOND on db servers.