Checking for Overlapping Data (Date Peroids)

[Andrew33]

Hi,

Does anybody know how to check for overlapping data in a before update event? An example: table one has a clients (tblclients.clientID) and table two has a cases (tblcase.caseID) with a start date (tblcase.startdate) and an end date (tblcase.enddate). When a user looks up a combo box to assign a client from table one (tblclients.clientID) to a case in tblcase and assign a unique date peroid with the startdate and enddate how do you stop the user from adding the client to another case when they are already assigned to a case that spans some part of the same time peroid?

e.g. Smith is assigned to case ID 1, startdate 1/2/2009 to enddate 5/2/2009 and user wants to assign the same client Smith to case ID 2, startdate 3/2/2009 to enddate 7/2/2009.

P.S. ( the same client can be assigned to another case as long as the time peroid does not over lap.)

It can be done in VBA but I'm struggling to get it to work.

Thanks for your help in advance.

Cheers,
Andrew

 

[RuralGuy]

Perhaps you will find this link useful:

http://allenbrowne.com/appevent.html

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[MajP]

That post kind of sums it up. This is how I do it.

1) develop a query that returns all conflicts
2) use a dcount to determine if the number of conflicts is > 0.

This way you can show the conflicts if needed, or just determine the number of conflicts is zero and thus no conflicts.

Here is the logic. Assume you have entered your new range with start date A and end date B.

A|-----------|B

Then a record with start date x and end date y could be any of these possibilites

A|-----------|B

x|-----|y 1 ends before range
x|-----|y 2 starts before ends inside
x|---|y 3 starts and ends inside
x|-----|y 4 starts inside ends outsid
x|-----|y5starts afterwards
x|------------------------------------|y
6 spans range

You want to look for conflicts so you do not care about case 1 or 5 since they are outside the range.
I will call A rangeStart, B rangeEnd, X startDate, Y endDate


Case 2 is determined by if A <= Y <= B or
rangeStart <= endDate and endDate <= rangeEnd

Case 3 I do not have to worry about because it is always true if case 2 is true or case 4 is true.

Case 4 is determined by if A<= X <=B or
rangeStart <= startDate and startDate <= rangeEnd

Case 6 is determined by if X<=A And B<=Y
startDate <= rangeStart and rangeEnd <= endDate

so set up some functions that you can use in a query. You could just use your control names but that gets hard to maintain.

Public glblRangeStart As Date
Public glblRangeEnd As Date
Public glblClientID As Date

Public Function getRangeStart() As String
  getRangeStart = glblRangeStart
End Function

Public Function getRangeEnd() As String
 getRangeEnd = glblRangeEnd
End Function

Public Function getClientID() As Long
  getClientID = glblClientID
End Function

Now prior to checking for conflicts set the global variables

Private Sub Command8_Click()
  Dim frm As Access.Form
  Set frm = Forms("frmDates")
  glblRangeStart = CDate(frm.txtBxStart)
  glblRangeEnd = CDate(frm.txtBxEnd)
  glblClientID = frm.cmboClientID
  frm.subfrm1.Requery
End Sub

Now you query

SELECT 
 tblClientCases.clientID, 
 tblClientCases.caseID, 
 tblClientCases.startDate, 
 tblClientCases.endDate
FROM 
 tblClientCases
WHERE 
  'Case2
  (((tblClientCases.clientID)=getClientID()) AND     
  ((tblClientCases.startDate)>=getRangeStart() And
  (tblClientCases.startDate)<=getRangeEnd())) 
  'case4
  OR
  (((tblClientCases.clientID)=getClientID()) AND
  ((tblClientCases.endDate)>=getRangeStart() And
  (tblClientCases.endDate)<=getRangeEnd())) 
  'case 6
  OR
  (((tblClientCases.clientID)=getClientID()) AND
  ((tblClientCases.startDate)<=getRangeStart()) AND
  ((tblClientCases.endDate)>=getRangeEnd()));

Now do a dcount to verify no conflicts.