Raise an error when dates overlap

[techwonder]

Have a database with 3 different Hotels, 3 different room numbers a DateFrom and DateTo.

Need to create a BEFORE CHANGE trigger that will not let me enter booking dates within a DATEFROM and DATETO range existing in other fields.

Can't put actual dates in the Macro since not a constant. Need it to 'read' all the parameters first.

So for example Hotel 1, room 143 has dates 6/12/14 - 6/18/14 booked. Change should stop me when I enter 6/13/14 on any row that has the same HotelNumber and Room Number

[June7]

Use DLookup like:

If Not IsNull(DLookup("ID", "Bookings", "#" & Me.tbxDateFrom & "# BETWEEN [DateFrom] AND [DateTo] AND HotelID=" & Me.tbxHotelID & " AND RoomID=" & Me.tbxRoomID)) OR Not IsNull(DLookup("ID", "Bookings", "#" & Me.tbxDateTo & "# BETWEEN [DateFrom] AND [DateTo] AND HotelID=" & Me.tbxHotelID & " AND RoomID=" & Me.tbxRoomID)) Then
'do this
End If

Or open a recordset object using that same filter criteria. If the recordset is empty then there is no conflict.

[pbaldy]

I think it's more complicated than that:

http://www.baldyweb.com/OverLap.htm

[techwonder]

Use DLookup like:

If Not IsNull(DLookup("ID", "Bookings", "#" & Me.tbxDateFrom & "# BETWEEN [DateFrom] AND [DateTo] AND HotelID=" & Me.tbxHotelID & " AND RoomID=" & Me.tbxRoomID)) OR Not IsNull(DLookup("ID", "Bookings", "#" & Me.tbxDateTo & "# BETWEEN [DateFrom] AND [DateTo] AND HotelID=" & Me.tbxHotelID & " AND RoomID=" & Me.tbxRoomID)) Then
'do this
End If

Or open a recordset object using that same filter criteria. If the recordset is empty then there is no conflict.

I think this is the right idea but I am not certain what to do with the Me.tbx part? This class is so basic that is all greek to me.

[techwonder]

I think it's more complicated than that:

http://www.baldyweb.com/OverLap.htm

Haha.. yes I read this before I posted. I appreciate it. It helps lay it out in terms of explanation but the syntax of my specific problem is what is confusing me. In other words the whole table could be blank except for one date range which would be the only thing being read. Does your solution work with that?

[pbaldy]

Well, the goal is to find out if anything conflicts with what you have on the form, so that query should work (adding the other criteria of course).