No message box appears no matter what I put in. Everything else works perfectly and "compile" accepts this code. Extremely annoying.

Look at the operators in the S variable. They are both less than. The first operator should be greater than StartDate.

I think the operators are correct as they are, as this should actually be the case with "overlap" (even if the comment "fall within" does not fit).

The following case should probably be avoided:
The new reservation starts before an existing reservation, but ends after this existing reservation.
In this respect, SD is before the previous StartDate and the previous EndDate is before the new ED.

@Roger

Looking at what you have:

S = "#" & SD & "#    ID = Nz(DLookup("ProductReservationID",

1. You haven't completed the code for the "S" variable from the video (there's quite a bit omitted)
2. You haven't closed the string for "S" so all of the information for your "ID" variable is enclosed in the "S" string

If it's just a copy/paste issue and the code is complete in your project, please accept my apologies but this jumped out as an obvious problem as it shows up in this post

Sorry Don the paste feature must have cut off what i originally copied. Here is what i thought I posted:

    ' Start date cannot fall within another reservation
    S = "#" & SD & "<#StartDate AND EndDate<=#" & ED & "#"
    ID = Nz(DLookup("ProductReservationID", "ProductReservationT", _
        "ProductID=" & PID & " AND ProductReservationID<>" & ProductReservationID & " AND " & S), 0)
    If ID <> 0 Then
       MsgBox "New reservation overlaps an existing one"
       Cancel = True
       Exit Sub
    End If

I see much was cut off in my original post.

I've tried Kevin's option of addd > (greater than) to #StartDate and it still doesn't work

Donald
The now missing characters were still in place a few hours ago. :-)
But it's probably true that some of Richard's code has disappeared between ‘Start date cannot fall within another reservation’ and this actual code, which seems to refer to overlapping reservations.

Roger
Have you tried an excessively early start date and an excessively late end date?
In the video, the MsgBox was actually displayed when Richard tried out a few dates.

Roger

OK, then from the updated shot, it seems like your code is out of order:

   S = "#" & SD & "<#StartDate AND EndDate<=#" & ED & "#"

Shouldn't it be:

    S = "StartDate<=#" & SD & "# AND EndDate<=#" & SD & "#"

The code pasted starts the delineation of # and puts the date value in then has the < symbol before the end of the delineation of the end of the date code and then the text for StartDate so the code would be lost. Also you're using "ED" instead of "SD" as the end date. This section of the code in the video is specifically checking to make sure that the Starting Date doesn't fall inside of another reservation.

Oh, and shouldn't it be: EndDate >#

Donald
Roger's code is the code for overlapping reservations ("this reservation cannot overlap another one") not the code for "start date cannot fall inside another reservation".
Also recognisable by the text for the MsgBox: "New reservation overlaps an existing one"
However, the code itself should be correct (at least nobody here has yet proved me wrong ;-)).

Sorry Don, thanks so much for trying so hard but still no solution. Thanks Kevin and Lars as well. I give up.

Lars

Ahh, I was looking at the comment in his post. That said, the code still is mistyped:

    ' Start date cannot fall within another reservation
    S = "#" & SD & "<#StartDate AND EndDate<=#" & ED & "#"

Should then be
    S = "#" & SD & "#<StartDate AND EndDate<=#" & ED & "#"

Notice the placement of the less than symbol in the StartDate portion

Donald
Well observed.
I hadn't even noticed the typo.

No luck with either suggestion from you and Sandra, thank you both so much.

So, I checked the code against the download copy and if you fixed the <# to #< then the code in that area should be working. Maybe if you can show us the code above (basically each block) we might be able to help you figure out what's causing your conundrum.

Otherwise, sorry wasn't helpful.

I definitely did not want to do this but I copied the code from Richard's example and it works perfectly.

    ' start date cannot fall inside another reservation
    S = "StartDate<=#" & SD & "# AND #" & SD & "#<EndDate"
    ID = Nz(DLookup("ProductReservationID", "ProductReservationT", _
        "ProductID=" & PID & " AND ProductReservationID<>" & ProductReservationID & " AND " & S), 0)
    If ID <> 0 Then
        MsgBox "Start date for new reservation conflicts with an existing one."
        Cancel = True
        Exit Sub
    End If
    End Sub

Ahh, glad you got it to work. I think what the problem was then is that you had the code from two different sections combined. As Lars had pointed out to me originally, I was going off of the comment at the top of the code 'start date.... but the msgbox under it was referencing the "New reservation overlaps an existing one" so I think you may originally had the code from 2 or 3 different date checks merged together.

What you are saying makes complete sense as there had to be some overlap but it's so strange, I asked ChatGPT the difference between the original code I wrote (according to Developer 24) and the one I copied from Richard's lesson 24 VBA code and the answer I got was that they were both exactly the same but I just couldn't get the original code to work (had to be what you and Lars were saying) and maybe I was doing something wrong in the presentation of the dates, really WEIRD!!! I'll admit, it was my bad and I am sorry for wasting your time Donald but I sure appreciate it, thank you.

No waste at! Enjoyed the challenge and distraction, lol