Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Microsoft Access 2010 - Timesheet database Catch overlap time entries except for

Posted on 2013-11-29
6
Medium Priority
?
776 Views
Last Modified: 2013-11-29
I have a database that employees enter their time on a certain project and it will warn them if they submit the time and it overlaps the time on another project. For example I enter 7/29/2013 7:00 AM start time and enter 7/29/2013 8:00 AM stop time. Then on another project I enter 7/29/2013 7:50 AM start time and enter 7/29/2013 9:00 AM it is a conflict. (7:50 to 8:00) Works great no issues. Now they want to be able to enter start time 7/29/2013 8:00 AM and stop time 7/29/2013 9:00 AM and this not being a conflict.  What is the best way to handle this.
Here is the current query.
SELECT tbltimesheet.EventID, tblEvent_1.EmpName, tbltimesheet.LocationID, tbltimesheet.Starttime, tbltimesheet.Stoptime, tbltimesheet.Sel, tbltimesheet.systemnotesid
FROM tbltimesheet INNER JOIN tbltimesheet AS tblEvent_1 ON tbltimesheet.EmpName = tblEvent_1.EmpName
WHERE (((tblEvent_1.EmpName)=networkusername()) AND ((([tblEvent_1].[Starttime]>=[tbltimesheet].[StopTIme]) Or ([tblEvent_1].[StopTime]<=[tbltimesheet].[Starttime]) Or ([tbltimesheet].[EventID]=[tblEvent_1].[EventID]))=0))
ORDER BY tbltimesheet.Starttime;
Thanks for all the help.
0
Comment
Question by:marlind605
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39685349
check if the method used here,
 http://support.microsoft.com/?kbid=245074 is applicable to your  problem..
0
 

Author Comment

by:marlind605
ID: 39685372
capricorn1 from the website I see "By subtracting 1 day from the reservation checkout date, you can book a date that is the last day of an existing reservation. " How would I change the query to allow for the same time?
0
 
LVL 58
ID: 39685430
Please read the following:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28176550.html#a39301783

It's exactly what you need.

Jim.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 58
ID: 39685435
This comment is probably the most consise in expaling the logic required:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28176550.html#a39301962

Jim.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39685440
and just to be clear, the check is:

WHERE (existing start date) <= New END Date AND (existing end date) >= New START date


 New END / START would be the time sheet your just entering.

Jim.
0
 

Author Closing Comment

by:marlind605
ID: 39685735
Jim, that was awesome. Thank you.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

577 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