OfficeFrustration - View Single Post

**guzarva16** · September 27th, 2008, 08:02 AM posted to microsoft.public.access

"wjn london" wrote in message
...
Paul,
Thanks a lot for the help. Between you and peter I now have lots of
options
and will spend some time experimenting with the code. Of course there may
be
more than 1 booking per day of varying lengths but i get the idea of using
the select statement to scan the bookings table and the logic to check for
clashes.

thanks again
warren

"Paul Shapiro" wrote:

Actually, I think the logic can be simplified a bit. To avoid a conflict,
the new start time must be greater than the existing end time, or the new
end time must be before the existing start time. So a conflict is the
netative of this.

strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy
hh:nn") & "# "
strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy
hh:nn") &
"# "

strSQL = "Select * From tblBooking Where NOT (" _
& strProposedStart & " endDateTime Or & strProposedEnd
startDateTime);"

"Paul Shapiro" wrote in message
...
You can check in the BeforeUpdate routine for the new row. If you want
to
identify the existing conflicting booking, you could use something like
this:

strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy
hh:nn") & "# "
strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy
hh:nn")
& "# "

strSQL = "Select * From tblBooking Where " _
& strProposedStart & " Between startDateTime And EndDateTime " _
& "Or " & strProposedEnd & " Between startDateTime And EndDateTime " _
& "Or (" & strProposedStart & " startDateTime And " & strProposedEnd
& "
EndDateTime);"

The logic identifies a conflicting row as one whe
a) the new start is within an existing booking
or b) the new end is within an existing booking
or c) the new booking "surrounds" an existing booking

Opening a recordset with this expression returns any overlapping
existing
row(s), which you can choose to display to the user and then cancel the
update. If the recordset is empty, there's no conflict. If you only
want
to know if there is a conflict, and don't need the details, use a Count
function and check for greater than zero.

"Dominic Vella" wrote in message
...
Yes that is tricky

Well, whilst I can think of some technical ways of doing this, it may
be
best to add a "Check Availability" button. In there I would write a
routine to do the check.

Without knowing your field and table names it would be difficult to
explain any suggested code.

Dom.
www.effectivedata.com.au

"wjn london" wjn wrote in message
...
I am developing a db for my local community centre. They hire out some
of
thier facilities. In the booking table and form i have various
validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2
bookings
can be booked on the same day, same start time and for the same
resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls
within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update"
and
somehow check if my new start time falls between any previously
entered
start-end times.
then cancel update if needed or allow update if no conflict.