To prevent collisions, the logic here is quite simple:
A collision occurs when:
RequestStartDat e <= 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. 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 dtRequeestStart Date as date
dim dtRequestEndDat e as date
dtRequestStartD ate = inputbox("Enter start Date")
dtRequestEndDat e = inputbox("Enter end date")
strWhere="#" & format(dtReques tStartDate,"mm/*dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtReques tEndDate,"mm/dd*/yyyy") & "# >= StartDate"
if dcount("*","tab leBooking",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.
The above also works well with time, or date + time fields....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com 
