Searching for intersecting date ranges
I am hoping that I might be able to ask for some assistance. I'm fairly new to filemaker (so far, so good) and am working on a small booking database for a local not for profit. I'm a mainframe developer by trade, so I spend most of my time working with JCL and REXX, and I find I'm having a little trouble shifting gears into the filemaker scripting language.
My problem is, I'm working on a setup to allow rooms to be booked (hotel or B&B type of thing). I've got most of it working fine, but what I would really like to do is to have a search criteria so that when a clerk is taking a booking, they can enter the "Check In" and "Check out" dates, and there would be a button that they could press beside this to display any bookings which occur in that time frame. I've got the search results layout set up, and am using it quite successfully with other searches, just this one with multiple criteria is messing with me.
Logically I know exactly what I am trying to do......
I need to take 4 values:
the check in date that was just entered (proposed checkin)
the check out date that was just entered (proposed checkout)
Existing check in dates from other bookings (check in)
Existing check out dates from other bookings (check out)
And I need to perform the following calculation to determine if a record should be displayed in the search form:
proposed checkin <= checkout and checkin <= proposed checkout
If this statement is true, then the booking intersects and should be displayed. I just cannot, for the life of me seem to make this work in a script or calculation.
Could anyone suggest anything to assist me? I really appreciate any help!