MS Access filter query based on time

Question

Would appreciate a help to on this. First of all I am not sure if this is a possibility with MS Access.

I would like my query to filter records based on time. For e.g: Below query showing employees checked-in to worksite and active at worksite (not checked-out). Note: check-in and check-out time are pre-entered into the table with each employee.

EmpID	Name	Check-inTime	Check-outTime
45	Peter	1400	1530

As per above query, I want filter those staff who has checked-in (when system time is 1400hrs)  and when they checked-out (when system time is 1530hrs) it should not appear in query. Can it be automatically filtered based on system time? Like, when system time is 1400hrs, the record should showup and when system time reaches to 1530hrs records should automatically disappear from query. And I can use this query as record source for my form.

Answer 1

Is this what you are attempting?

SELECT FirstName, LastName

FROM Employees INNER JOIN TimeLog

ON Employees.EmployeeID = TimeLog.EmployeeID

WHERE NOW() BETWEEN DATEADD("n",-15,WorkDate+TimeStart)

AND WorkDate+TimeEnd + IIF(TimeStart > TimeEnd,1,0);

Answer 2

Change it to:

SELECT FirstName, LastName

FROM Employees INNER JOIN TimeLog

ON Employees.EmployeeID = TimeLog.EmployeeID

WHERE NOW() BETWEEN WorkDate+TimeStart

AND WorkDate+TimeEnd + IIF(TimeStart > TimeEnd,1,0);

Answer 3

What is the datatype of your time fields?

Its Date/time format

Answer 4

What is the datatype of your time fields?

Answer 5

Hi,

perhaps you can use this expressions as criteria:

Check-inTime

<=left(time,2) & mid(time,4,2)

Check_outTime

>=left(time,2) & mid(time,4,2)

Ciao Mimmo