asked on

Find personnel logged in on a given day

I have a table that has controller login and logout times.
Format is General Date (9/3/2014 12:20:18 PM)
I am trying to design a query that asks for a date [date?] and then returns anyone logged in that date. (between 00:00 and 23:59 that date)

Here's the catch.
I might come to work at 8pm (LOGIN: 9/2/2014 20:00:00) and stay until 4am (LOGOUT: 9/3/2014 04:00:00).

when I get the date? prompt, I want to be able to type 9/3/2014 and have it return the login above. Same return if I ask for 9/2. Showing the login and logout time crossing the day.

So, for example, I get the date? prompt and type 9/2/14, I want:

Smith, 9/2/2014 08:00:00, 9/2/2014 08:00:00
Doe, 9/2/2014 20:00:00, 9/3/2014 04:00:00

and if I type 9/3/14, I also want:
Jones, 9/3/2014 09:14:00, 9/3/2014 08:00:00
Doe, 9/2/2014 20:00:00, 9/3/2014 04:00:00

Jan Louwerens

Something like:

SELECT * FROM <Table> WHERE
Login < <date_plus_1_day> AND
Logout >= <date>

TIgerV

ASKER

<date_plus_1_day>

How do I tell the query <date_plus_1_day>?

Dale Fye

Or you could use something like:

SELECT * FROM yourTable
WHERE DateValue([Login]) = [Enter date (mm/dd/yy)]
OR DateValue([LogOut]) = [Enter date (mm/dd/yy)]

Hamed Nasr

Try:
SELECT * FROM tbl
WHERE [selectedDate] BETWEEN LOGIN AND LOGOUT

Jan Louwerens

It looks like Access has a DateAdd function:

DateAdd("d", 1, <date>)

Open in new window

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial