Solved

Find personnel logged in on a given day

Posted on 2014-09-03
6
156 Views
Last Modified: 2014-09-10
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
0
Comment
Question by:TIgerV
6 Comments
 
LVL 5

Expert Comment

by:Jan Louwerens
ID: 40301870
Something like:

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

Author Comment

by:TIgerV
ID: 40301902
<date_plus_1_day>

How do I tell the query <date_plus_1_day>?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40301919
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)]
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 30

Expert Comment

by:hnasr
ID: 40301957
Try:
SELECT * FROM tbl
WHERE [selectedDate] BETWEEN LOGIN AND LOGOUT
0
 
LVL 5

Expert Comment

by:Jan Louwerens
ID: 40302103
It looks like Access has a DateAdd function:
DateAdd("d", 1, <date>)

Open in new window

0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40303247
This should do:

SELECT *
FROM tblYourTable
WHERE [Date?] BETWEEN INT(LOGIN) AND LOGOUT

The trick is that Int cuts the time part, thus makes 9/2/2014 08:00:00 to match an input of 9/2/2014.

/gustav
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question