Days overlap

[Lou699]

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

Private Sub Leave_Start_Date_BeforeUpdate(Cancel As Integer)
    Dim strDate As String
 
    'We format the date string to include the '#' delimiters
    strDate = Format(CDate(Me.Leave_Start_Date), "\#m/d/yyyy\#")
    Cancel = DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
                  "[Trial]", _
                  "([Staff Number] =" & Me.Staff_Number & ")") Or _
             DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
                  "[Trial]", _
                  "([Staff Number]=" & Me.Staff_Number & ")")
    If Cancel Then _
        Call MsgBox("This date overlaps with an existing date range", _
                    vbOKOnly)

End Sub

[Lou699]

Private Sub Leave_Start_Date_BeforeUpdate(Cancel As Integer)
Dim strDate As String

'We format the date string to include the '#' delimiters
strDate = Format(CDate(Me.Leave_Start_Date), "\#m/d/yyyy\#")
Cancel = DMax("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
"[Trial]", _
"([Staff Number] =" & Me.Staff_Number & ")") Or _
DMin("(" & strDate & " Between [Leave Start Date] And [Leave End Date])", _
"[Trial]", _
"([Staff Number]=" & Me.Staff_Number & ")")
If Cancel Then _
Call MsgBox("This date overlaps with an existing date range", _
vbOKOnly)

 End Sub

Code Adjustment

[cactusdata]

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

Dim ThisStartDate   As String
Dim ThisEndDate     As String
Dim Criteria        As String
Dim Cancel          As Boolean

ThisStartDate = "#" & Format(Me!Leave_Start_Date, "yyyy\/mm\/dd") & "#"
ThisEndDate = "#" & Format(Me!Leave_End_Date, "yyyy\/mm\/dd") & "#"
Criteria = "[Staff Number] = " & Me!Staff_Number & " And " & _
    "[Leave Start Date] <= " & ThisEndDate & " And [Leave End Date] >= " & ThisStartDate & ""
Cancel = Not IsNull(DLookup("[Staff_Number]", "[Trial]", Criteria))

[Lou699]

Thanks a lot it's working <3

[twinnyfo]

cactusdata got the skills!!!

[DanicaDear]

Is it appropriate for me to ask a question with my own code which I used this thread to build?
I figure the experts are already on this thread. I am trying to accomplish the exact same thing where "Condo" replaces "Staff Member" and Arrival and Departure are names of my start and end dates.

Arrival and Departure are Date/Time Fields. Condo is a short text field.
I made a button to click to run the check after I enter Arrival, Departure, and Condo onto my form. (The screen shot attached may be helpful.)

Private Sub btnCheckDates_Click()

Dim ThisStartDate    As String
Dim ThisEndDate     As String
Dim Criteria             As String
Dim Cancel              As Boolean
 
ThisStartDate = "#" & Format(Me!Arrival, "yyyy\/mm\/dd") & "#"
ThisEndDate = "#" & Format(Me!Departure, "yyyy\/mm\/dd") & "#"
Criteria = "[Condo] = " & Me!Condo & " And " & _
    "[Departure] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""
Cancel = Not IsNull(DLookup("[Condo]", "[qryBookings]", Criteria))

End Sub

I have an issue at

Criteria = "[Condo] = " & Me!Condo & " And " & _

specifically the [Condo] in brackets. I get a run-time error when I run it:
The expression you entered as a query parameter produced this error: 'A104'
(A104 is the condo I chose for the test.). If I change [Condo] to [test] I get the same run time error with "test" in the error instead of A104. I am attaching a screen shot for clarity.

The "Trial" in the original post threw me way off because I wasn't sure what that was supposed to be. I inserted my query name there that contains all my bookings for ALL condos. I hope that was correct.

Can someone help me resolve this error so I can try to fire this code and detect my double bookings!?? :-)

Also, if I may ask: The post said that this will check for BOTH arrival and departure with this one piece of code. Is that correct if I am running it from a button, and not on BOTH the arrival AND the departure fields when updated? I feel that I may have taken something and oversimplified it. I need to check that the arrival date is not in the range of another reservation, and then I also need to check that the departure date is not in the range of another reservation. And I also need to make sure no dates overlap---like the original poster's example... if I have a reservation existing already for Oct. 10-15, and then I make a reservation for Oct. 8-17, the arrival date and departure date would pass the check but the middle dates are still overlapped.

Thanks in advance for anyone's time and help!

[cactusdata]

Condo is alphanumeric (text), it seems, thus quotes are needed. So, try:

Criteria = "[Condo] = '" & Me!Condo & "' And " & _

[DanicaDear]

cactusdata. Thank you. I did get the code to fire and I added a message box
Just for anyone else looking in the future, here was my final piece. This is BRILLIANT and will help me so much. Thank you soo much; I am so grateful!

Private Sub btnCheckDates_Click()

Dim ThisStartDate   As String
Dim ThisEndDate     As String
Dim Criteria        As String
Dim Cancel          As Boolean
 
ThisStartDate = "#" & Format(Me!Arrival, "yyyy\/mm\/dd") & "#"
ThisEndDate = "#" & Format(Me!Departure, "yyyy\/mm\/dd") & "#"
Criteria = "[Condo] = '" & Me!Condo & "' And " & _
    "[Departure] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""
Cancel = Not IsNull(DLookup("[Condo]", "[qryBookings]", Criteria))
If Cancel Then _
Call MsgBox("Double Booking", _
vbOKOnly)

End Sub

[DanicaDear]

I'm very sorry that I'm back.
As I began testing this with data, nothing is firing correctly.

I made a reservation for 1/1/25-1/5/25.
I tested these dates. I never got any message box.
Tests:
12/31/24 – 1/1/25 fires correctly. This is NOT a double booking, therefore I don't expect a message box.
12/31/24 - 1/2/25 does not detect double booking
12/31/24 – 1/3/25 does not detect double booking
12/31/24 – 1/4/25 does not detect double booking
12/31/24 – 1/5/25 does not detect double booking
12/31/24-1/6/25 does not detect double booking
1/1/25 – 1/2/25 does not detect double booking
1/1/25 – 1/3/25 does not detect double booking
1/1/25-1/4/25 does not detect double booking
1/1/25 – 1/5/25 does not detect double booking
1/1/25 – 1/6/25 does not detect double booking
1/2/25 - 1/3/25 does not detect double booking
1/2/25-1/4/25 does not detect double booking
1/2/25 – 1/5/25 does not detect double booking
1/2/25-1/6/25 does not detect double booking
1/3/25-1/4/25 does not detect double booking
1/3/25-1/5/25 does not detect double booking
1/3/25 – 1/6/25 does not detect double booking

Do you see anything else that was wrong? I had some double booking message boxes a while ago but when I saved and came back later, now I can't get ANY! My code is posted in the previous thread, but I'm still not confident that code will completely work for what I'm testing because even on my first test it did not detect everything correctly.
If me.Arrival OR Me.Departure falls within any previously saved range of arrival - departure for the Me.Condo in qryBookings, I need the message box to fire. It WOULD BE OK if Me.Arrival = another Departure and Me.Departure = another arrival (because people do come and go on the same day).

[cactusdata]

You miss to check for both Arrival and Departure.
See the answer above.

[DanicaDear]

I do apologize!
I found a couple errors. One being my "on Click" event procedure went missing randomly...so the clicking wasn't running anything. Hence nothing being deteched.

Second error, I had departure in my code twice.

 "[Departure] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""

Should be

 "[Arrival] <= " & ThisEndDate & " And [Departure] >= " & ThisStartDate & ""

and from what I can tell this one piece of code on a button will check both the start and the end dates in one test! I am using it!!! THANK YOU SO MUCH!!!