Booking out a Guest suite , avoiding double booking in Access

[Guest]

I am trying to make available the facility to book out a guest suite shared
by 33 other apartments but need a means of validating the date(s) are
available based on existing bookings. I am prepared to link to an easy
interface in outlook if its possible but would prefer to stay in access. Can
anyone help, All i need is start date, end date and apartment number

 

[Douglas J Steele]

Allen Browne has some information about this at
http://www.allenbrowne.com/appevent.html

 

[Albert D.Kallal]

And, to prevent collisions, the logic here is quite simple:

A collision occurs when:


RequestStartDate <= EndDate
and
RequestEndDate >= StartDate


The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking.

If your case, you would add the room number to the above....

RequestStartDate <= EndDate
and
RequestEndDate >= StartDate
and
RequestRoomNumber = RoomNumber


In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date


dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"


if dcount("*","tableBooking",strW­here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....

The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. Howver, what is nice here is that
the simple condistion above does return ANY collsion. A very easy problem to
solve...

--