This is the simplest function (UDF) to calculate duration from two times.
Put this in a standard module
Code:
Public Function fCalcWorkHours(pStartTime As Variant, pEndTime As Variant)
fCalcWorkHours = Round(DateDiff("n", pStartTime, pEndTime) / 60, 2)
End Function
The first query would be
Code:
SELECT WorkHours.EmployeeID, WorkHours.WrkDate, WorkHours.WrkStart, WorkHours.WrkEnd, fCalcWorkHours([WrkStart],[WrkEnd]) AS WkHrs
FROM WorkHours;
Note: it doesn't appear that the break time was taken into consideration in the text file example records!
The total query would be
Code:
SELECT [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate, Sum([qryCalcWrkHours].WkHrs) AS SumOfWkHrs
FROM qryCalcWrkHours
GROUP BY [qryCalcWrkHours].EmployeeID, [qryCalcWrkHours].WrkDate;
-------------------------------------------------------------------------------------------------------------------------
Query results