Help Needed To Prevent Double Booking

[Wayne]

I'm building a bookings database for trucks that among other things
captures the TruckName, LoadDate, LoadTime, UnloadDate and UnloadTime.

Is there a simple way to prevent double bookings for any particular
truck so that when a new entry is being added, if the LoadDate and
LoadTime doubles up with an existing entry I can flag it to the user?

[Allen Browne]

Use the BeforeUpdate event procedure of the *form* where you enter the data.
(Be sure to use the vent of the form, not that of the controls.)

The idea is to use DLookup() to see if another clash exists in the table. A
clash is defined as:
- that one starts before this one ends, AND
- this one starts before that one ends, AND
- it's the same truck, AND
- it's not the same record.

It would be easier if you combined the dates and times into the one field,
i.e. use 2 date/time fields only - say LoadDateTime and UnloadDateTime. If
you don't want to do this, add the 2 values together, i.e. LoadDate +
LoadTime.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Wayne" <cq*******@volcanomail.comwrote in message
news:15**********************************@s8g2000p rg.googlegroups.com...

I'm building a bookings database for trucks that among other things
captures the TruckName, LoadDate, LoadTime, UnloadDate and UnloadTime.

Is there a simple way to prevent double bookings for any particular
truck so that when a new entry is being added, if the LoadDate and
LoadTime doubles up with an existing entry I can flag it to the user?

[Albert D. Kallal]

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. 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.

The above also works well with time, or date + time fields....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Wayne]

Thanks Allen and Albert. I'm working through this.