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
Solved

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

Posted on 2013-11-29
6
713 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 57
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 57
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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

791 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