By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,553 Members | 863 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,553 IT Pros & Developers. It's quick & easy.

Days overlap

P: 9
Hey Guys

I'm trying to set up a form to register employee vacations without overlapping days it worked fine however in the below case it doesn't detect an overlap

If I entered a vacation from Oct 2nd 2020 until Oct 4th 2020
Then I entered vacation from Oct 1st 2020 until Oct 5th 2020 for the same employee ID

I've done the code for both text box Start & end

I want it to detect this sort of overlap when I adjust the Leave End Date box

Expand|Select|Wrap|Line Numbers
  1. Private Sub Leave_Start_Date_BeforeUpdate(Cancel As Integer)
  2.     Dim strDate As String
  3.  
  4.     'We format the date string to include the '#' delimiters
  5.     strDate = Format(CDate(Me.Leave_Start_Date), "\#m/d/yyyy\#")
  6.     Cancel = DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
  7.                   "[Trial]", _
  8.                   "([Staff Number] =" & Me.Staff_Number & ")") Or _
  9.              DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
  10.                   "[Trial]", _
  11.                   "([Staff Number]=" & Me.Staff_Number & ")")
  12.     If Cancel Then _
  13.         Call MsgBox("This date overlaps with an existing date range", _
  14.                     vbOKOnly)
  15.  
  16. End Sub
2 Weeks Ago #1

✓ answered by cactusdata

You can check both start and end date in one go:

Expand|Select|Wrap|Line Numbers
  1. Dim ThisStartDate   As String
  2. Dim ThisEndDate     As String
  3. Dim Criteria        As String
  4. Dim Cancel          As Boolean
  5.  
  6. ThisStartDate = "#" & Format(Me!Leave_Start_Date, "yyyy\/mm\/dd") & "#"
  7. ThisEndDate = "#" & Format(Me!Leave_End_Date, "yyyy\/mm\/dd") & "#"
  8. Criteria = "[Staff Number] = " & Me!Staff_Number & " And " & _
  9.     "[Leave Start Date] <= " & ThisEndDate & " And [Leave End Date] >= " & ThisStartDate & ""
  10. Cancel = Not IsNull(DLookup("[Staff_Number]", "[Trial]", Criteria))

Share this Question
Share on Google+
4 Replies

P: 9
Expand|Select|Wrap|Line Numbers
  1. Private Sub Leave_Start_Date_BeforeUpdate(Cancel As Integer)
  2. Dim strDate As String
  3.  
  4. 'We format the date string to include the '#' delimiters
  5. strDate = Format(CDate(Me.Leave_Start_Date), "\#m/d/yyyy\#")
  6. Cancel = DMax("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
  7. "[Trial]", _
  8. "([Staff Number] =" & Me.Staff_Number & ")") Or _
  9. DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
  10. "[Trial]", _
  11. "([Staff Number]=" & Me.Staff_Number & ")")
  12. If Cancel Then _
  13. Call MsgBox("This date overlaps with an existing date range", _
  14. vbOKOnly)
  15.  
  16.  End Sub
Code Adjustment
2 Weeks Ago #2

cactusdata
Expert 100+
P: 128
You can check both start and end date in one go:

Expand|Select|Wrap|Line Numbers
  1. Dim ThisStartDate   As String
  2. Dim ThisEndDate     As String
  3. Dim Criteria        As String
  4. Dim Cancel          As Boolean
  5.  
  6. ThisStartDate = "#" & Format(Me!Leave_Start_Date, "yyyy\/mm\/dd") & "#"
  7. ThisEndDate = "#" & Format(Me!Leave_End_Date, "yyyy\/mm\/dd") & "#"
  8. Criteria = "[Staff Number] = " & Me!Staff_Number & " And " & _
  9.     "[Leave Start Date] <= " & ThisEndDate & " And [Leave End Date] >= " & ThisStartDate & ""
  10. Cancel = Not IsNull(DLookup("[Staff_Number]", "[Trial]", Criteria))
2 Weeks Ago #3

P: 9
Thanks a lot it's working <3
2 Weeks Ago #4

twinnyfo
Expert Mod 2.5K+
P: 3,548
cactusdata got the skills!!!
2 Weeks Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.