Solved

Find personnel logged in on a given day

Posted on 2014-09-03
6
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 48

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
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 
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 50

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

729 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