how to check for over lapping time periods

hey guys,

i've got a table for how staff spend their time in the day. below is an example.

we can see that lunch overlaps with the 2nd day work.

when users, try and key in a time period, i want to ensure that there is no overlap. if there is an overlap, i want to raise a promp to disallow them from adding the new time period. a time period can start from when the last period ended e.g. 12pm - 2pm, 2pm to 6pm. doesn't need to be 2:01pm.

what's the best way to do this?

SQL / VBA? i can do it with a lot of code in vba (k haven't thought exactly how to do it but it would require loops and all), but i have a really, really, really big suspicion that SQL can do this quite easily with joins and criteria.

hrmm i'm really not very good with SQL right now and now is the time when all the SQL is coming together (yay!! haha), so could someone help me out with the SQL or VBA if it's not possible in SQL? thanks!! = ))

(if it's VBA then just general directions will do, i can probably figure it out myself, just a matter of how optimised it is in VBA or not)

StartTime      EndTime      Category
9:00 AM      12:00 PM      Work
12:00 PM      2:00 PM      Lunch
1:00 PM      6:00 PM      Work


thanks guys!! = ))
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Have you considered slowing down a bit on your posts?

Some of your questions themselves are "Overlapping".

Meaning the solution in one Q, may change the circumstances of another Q...

For example, you have not yet received an answer on your "missing days" post, so that may effect what you are asking here.

JeffCoachman
0
developingprogrammerAuthor Commented:
oh really? i didn't think of that! as in i didn't think the missing day post would solve this... err... wait... hrmm.. yea i guess if i use the start and end... hrmm ok ya i guess it would indirectly solve this. sorry Jeff i couldn't see so far ahead!! (far ahead for myself haha)  = ))
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Here's a great thread on overlaps:

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

 Make sure your read down to the end of the thread as fyed had a great solution.

 I also have code to check start/end time overlaps (time card form), if the SQL checks won't work for you.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
0
developingprogrammerAuthor Commented:
argh! complex stuff i need to load into my brain at 3 am in the morning!! haha

but really really good stuff Jim!!

ok let me try and digest it now = )
0
developingprogrammerAuthor Commented:
hrmm ok since this is a check (BUT a super necessary check), i have to finish the module first before putting this "bell and whistle" on. well it isn't a bell and whistle. but i'm so short of time i gotta finish this thingy by tomorrow afternoon = (((

but super super good stuff Jim, i WILL fully digest and reply = ))

Jim this question is a lot more urgent - could you help me take a look? after reading the post you directed me to a little, i think that the answers for these 2 questions are different. one needs to generate records, one is a check = ) thanks Jim!!

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28234613.html#a39477346
0
developingprogrammerAuthor Commented:
hrmm Jim perhaps you can also share your VBA code for checking? i think i MIGHT need to go down the VBA path here. hrmm i'm not sure. but yup i definitely want to learn from your VBA code as well = )

thanks Jim!! = ))
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
This is from an old time card app.  User would input a start date, time, and end time.

Private Function CheckForOverlap(strTime As String) As Integer
 
  Dim rst As Recordset
  Dim curStartTime As Currency
  Dim curStopTime As Currency
  Dim curRecordStartTime As Currency
  Dim curRecordStopTime As Currency
  Dim lngCurTranID As Long
  Dim fAtNewRecord As Integer

  ' Routine to check if new time entry overlaps an existing time card line.
  CheckForOverlap = False
  On Error Resume Next
  lngCurTranID = Me![TranID]
  fAtNewRecord = (Err = 3021)

  ' Get start and stop times for current line.
  curStartTime = NZ(Me![txtStart], "0")
  curStopTime = NZ(Me![txtStop], "0")
  If curStopTime <= curStartTime Then curStopTime = curStopTime + 2400
 

  ' Check all records in clone.
  Set rst = Me.RecordsetClone
  If rst.RecordCount > 0 Then
    rst.MoveLast
    Do Until rst.BOF
      If fAtNewRecord = False Then
        If lngCurTranID <> rst![TranID] Then
          If Not (NZ(rst![Start], "") = "") Then
            curRecordStartTime = NZ(rst![Start], "0")
            curRecordStopTime = NZ(rst![Stop], "0")
            If curRecordStopTime < curRecordStartTime Then curRecordStopTime = curRecordStopTime + 2400
            ' Debug.Print curStartTime, curRecordStartTime
            ' Debug.Print curStopTime, curRecordStopTime
            If curRecordStartTime > 0 And curRecordStopTime > 0 Then
              If strTime = "B" Then
                If curStartTime >= curRecordStartTime And curStartTime < curRecordStopTime Then
                  CheckForOverlap = True
                  Exit Do
                End If
                If curStopTime <> 2400 Then
                  If curStartTime < curRecordStartTime And curStopTime > curRecordStartTime Then
                    CheckForOverlap = True
                    Exit Do
                  End If
                End If
              Else
                If curStartTime < curRecordStartTime And curStopTime > curRecordStartTime Then
                  CheckForOverlap = True
                  Exit Do
                End If
              End If
            End If
          End If
        End If
      End If
      rst.MovePrevious
    Loop
  End If
 
  rst.Close
  Set rst = Nothing

End Function
0
developingprogrammerAuthor Commented:
whao cool thanks Jim!!

2 questions:

1) why did you use do until BOF and moveprevious instead of do until EOF and movenext? cause later records would probably be overlapping first?

2) ah, i realised i didn't communicate what i wanted to do in a lucid enough fashion. i want to use a bound form to add the new record if it's not overlapping. if it's overlapping then the user will be prompted that they can't add it.

is there any way to do this with a bound form? haha i realise that's the crux of my whole endeavor - bound forms! i keep remembering what you told me last time to try and use Access as how it's meant to be used so that we can develop rapidly - which is what Access is meant to do, so now i'm trying to figure out how to work with it instead of overriding its functions = ) so bound forms is where i start!! = ))
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<is there any way to do this with a bound form? >>

 That's exactly what the above code did.  It was a bound subform in continuous form view.

 User had just entered the date, start and end times and this code would be called from the afterupdate event of the controls (or beforeupdate event of the form).

  I'm comparing that against the other entires already made (if any).  I'm doing that based on the RecordsetClone, which allows me to move through the forms recordset without changing what the user sees (recordsetclone gives you a seperate cursor to move through the forms recordset).

 I started at the last entry and moved backwards to the first because yes, that's most likely where an overlap would occur.   The records were in date, start time sequence.

Jim.
0
developingprogrammerAuthor Commented:
ah, great Jim! so sorry my poor communication skills again!! haha, i mean is there any way to do that with the SQL? (this may be a really potentially dumb question! so sorry if the answer is so obvious and i'm not seeing it!!)
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<i mean is there any way to do that with the SQL? >>

 You would need to run a query against the existing records.....something that simply returns Count() would suffice or a DCount.

 You would need to have everything as a Date/Time data type.

 You would query the records for:

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

  Of course that works for date/times as well.

If you get any records returned, you have an overlap.

Jim.
0
developingprogrammerAuthor Commented:
thanks Jim!! as always your solutions are fantastic!! this code and SQL is really superb!! thanks Jim!! = ))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.