Help Build Query

[Guest]

Thanks for taking the time to read my question.

I don't know if this is possible but I need a query to tell me if my new
booking conflicts with any bookings already made. If the query returns
nothing, then there are no conflicts.

Here is an example I made up. I can't get all these scenarios to work.

Thanks,

Brad

Example:
Start Time relates to Start Date, and End Time relates to End Date
Query Returns nothing if no dates and times conflict.
Booking 1 can end at the same time booking 2 starts, or conversly booking 2
can start at the same time booking 1 ends


Current Bookings
ID StartDate EndDate StartTime EndTime
1 21-03-2006 21-03-2006 2:00 PM 5:00 PM
2 22-03-2006 22-03-2006 9:00 AM 3:00 PM
3 22-03-2006 22-03-2006 5:00 PM 11:00 PM

New Booking Examples
ID StartDate EndDate StartTime EndTime
1 22-03-2006 22-03-2006 3:00 PM 5:00 PM
2 22-03-2006 22-03-2006 12:30 PM 3:30 PM
3 22-03-2006 22-03-2006 7:00 AM 12:00 PM
4 21-03-2006 22-03-2006 4:00 PM 8:30 AM
5 21-03-2006 22-03-2006 7:00 PM 9:30 AM
6 21-03-2006 22-03-2006 5:00 PM 9:00 AM

Result
New Booking Example 1 Returns Nothing
New Booking Example 2 Returns Current Booking ID = 2
New Booking Example 3 Returns Current Booking ID = 2
New Booking Example 4 Returns Current Booking ID = 1
New Booking Example 5 Returns Current Booking ID = 2
New Booking Example 6 Returns Nothing

 

[Allen Browne]

Two events clash if:
- A begins before B ends, and
- B begins before A ends, and
- A and B are not the same event.

You could simplify this by using a single date/time field to store the date
and time instead of 2 fields. As it stands you would be looking at something
like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDateTime = "\#mm\/dd\/yyyy hh:nn:ss\#"

If IsNull(Me.StartDate) Or IsNull(Me.StartTime) Or _
IsNull(Me.EndDate) Or IsNull(Me.EndTime) Then
Cancel = True
MsgBox "All dates and times required."
Else
If (Me.StartDate = Me.StartDate.OldValue) And _
(Me.StartTime = Me.StartTime.OldValue) And _
(Me.EndDate = Me.EndDate.OldValue) And _
(Me.EndTime = Me.EndTime.OldValue) Then
'do nothing
Else
strWhere = "(ID <> " & Me.ID & ") AND (StartDate + StartTime < "
& _
Format(Me.EndDate + Me.EndTime, strcJetDateTime) & ") AND ("
& _
Format(Me.StartDate + Me.StartTime, strcJetDateTime) & _
" < EndDate + EndTime)"
varResult = DLookup("ID", "BookingTable", strWhere)
If Not IsNull(varResult) Then
If MsgBox ("Clash with event " & varResult & "." & vbCrLf &
_
" Continue anyway?", vbYesNo + vbDefaultButton2) <>
vbYes Then
Cancel = True
End If
End If
End If
End If
End Sub

If you want to compare every record against every other record to identify
clashes, see:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html

 

[Guest]

Hi Allen,

Thanks for the reply.

You should see the code I've already written for this. Reams and reams of
it. I am thinking there has to be an easier way. That's why I wanted to try
to do it in a query.

I'll try your code to see what happens and get back to you. Thanks for the
link too!

Brad

 

[Guest]

This worked perfectly!

Thanks so much Allen.

I've not seen Const strcJetDateTime = "\#mm\/dd\/yyyy hh:nn:ss\#" before.
What is it?

Thanks again,

Brad

 

[Allen Browne]

Since we are about to include a literal date time value in the string that
is effectively a WHERE clause in Access, we used the Format() function to
output the date/time value in the native JET format.

JET is the data engine in Access. It expects dates in month/day/year format,
and time values in hour:minute:second format, and it expects the # as the
delimiter. The backslashes cause the Format() function to treat the next
character as a literal, we we really get the hash, slash, and the colon,
regardless of what the user's regional settings might be.

If you live in the US, it will work if you just delimit the literal
date/time with #, but formatting with this string makes sure the code works
correctly anywhere on the planet.

 

[Guest]

Very Cool.

That is really going to come in handy.

Have a great day Allen,

Brad