Access No duplicates on Start time, End time and Time in between

[Florence]

Hi,

I created an access database for car reservation and am new at this.

How can I avoid the booking of the same car on the same day at the same
time, I have two fields "start" and "end" but if I create an index to not
allow duplicate on the index (car, date, start, and end) it doesn't count the
times in between start and end, how can I fix this.

Sorry about my ignorance, I'm self-taught and am trying to put this together
for my employer.

Thanks in advance for your help!

 

[Albert D. Kallal]

You can't just use a index to prevent this type of problem.

You will need your booking form to simply "look" for a collsions.

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

 

[Florence]

Thanks so much for your time, I'll try this right away!