Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

I would like to compute how many minutes or hours the employee left work by comparing two records

Status
Not open for further replies.

YoBi4Ever

Instructor
Joined
Jun 5, 2007
Messages
18
Location
KW
I have a database where each record contains IN and OUT. I would like to see how left for more than 20 minutes.
How can i check the OUT time in the first record with the IN time and then calculate the minutes?
Taking into considerations there are other days in the DB and i want to do it for each day seperate

InTimeOutTimeTotal minutes OUT ( i calculated this manually)
2024-12-16 06:11:072024-12-16 11:30:57
2024-12-16 11:49:302024-12-16 12:19:4511:30:57 to 11:49:30 = 00:19:27 (hh:mm:ss)
2024-12-16 12:33:582024-12-16 15:03:2012:19:45 to 12:33:58 = 00:14:13 (hh:mm:ss)
 
Examples:

? ALLTRIM(STR(CTOT('21/01/2025 18:00:00') - CTOT('21/01/2025 17:00:00'))) + ' seconds'
? ALLTRIM(STR((CTOT('21/01/2025 18:00:00') - CTOT('21/01/2025 17:00:00')) / 60)) + ' minutes'
? ALLTRIM(STR((CTOT('21/01/2025 18:00:00') - CTOT('21/01/2025 17:00:00')) / 60 / 60)) + ' hours'

Adjust based on your SET DATE setting
 
What you are talking about is a Duration between two dates, which you can calculate by using DateDiff Function
So, if you want to know the duration in Minutes ("n") between 2024-12-16 11:30:57 and 2024-12-16 11:49:30, you can do:

Code:
Dim datStart As Date
Dim datStop As Date

datStart = CDate("2024-12-16 11:30:57")
datStop = CDate("2024-12-16 11:49:30")
MsgBox DateDiff("n", datStart, datStop)
 
Last edited:
You will need a subquery or Domain function to calculate across records. This will give you the number of minutes (and fractions). You can do other calculations but the key is the subquery. I don't this this result can be edited which would require a domain function.

Code:
SELECT tblInOut.InTime, tblInOut.OutTime,
DateDiff("s",(SELECT Max(OutTIme) FROM tblInOut O WHERE O.OutTime < tblInOut.InTime),[inTime])/60 AS TotalMinOut
FROM tblInOut;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top