Booking Query

[Guest]

Hi all, (bit long i'm sorry

I've posted this before (on the forms page) but i think i may have more success here (no offence to the other MVP's

I am trying to create a query that will check the values entered into a forms combo boxes AGAINST a booking table and check to see if there are any duplicate entries

Easy and i've done this bit -

The hard bit is that i need to be able to distinguish between mulitple hire times for one aircraft within a given date

ie an aircraft "G-BGIY" on [HireDate] can be booked between the hours of #09:00:00# and #10:00:00# but also between #12:00:00# and #13:00:00# - how do i change the following code to allow these two bookings to occur?

--------Code is--------

Private Sub ConfirmBooking_Click(
Dim strSql As Strin
Dim rstFound As DAO.Recordse

strSql = "Select AcReg FROM BookingDetails where [AcReg] = '" & cboAcReg & "'"
& " AND ([HireDate] = #" & cboHireDate & "# "
& " AND [StartTime] >= #" & cboStartTime & "# AND [EndTime] <= #" & cboEndTime & "#)

Set rstFound = CurrentDb.OpenRecordset(strSql

If rstFound.RecordCount > 0 The
Call MsgBox("Sorry this aircraft is already booked, please choose another time", vbCritical, "Error")
Els
Call MsgBox("Your booking is confirmed", vbExclamation, "Booking Confirmed"
DoCmd.GoToRecord , , acNewRe

End I

End Su

--------TABLES---------
tbl BookingDetails
AcRe
HireDat
StartTim
EndTIm

------QUERY-------

SELECT BookingDetails.MemberNo, BookingDetails.AcReg, BookingDetails.HireDate, BookingDetails.StartTime, BookingDetails.EndTim
FROM BookingDetails

This is the query i want to use the given code to find any duplicates from - at the moment it seems to get stuck on the HireDate field and will not allow 2 entries for the same day

Thanks in advance
Cam

 

[Michel Walsh]

HI,


Sounds like a TABLE design problem, your HireDate not allowing duplication.
Change the index on that field to allow duplication.

(In general, it is preferable to store the day WITH the time in the same
field. As example, in your case, you have a problem around midnight).



Hoping it may help,
Vanderghast, Access MVP

Hi all, (bit long i'm sorry)

I've posted this before (on the forms page) but i think i may have more

success here (no offence to the other MVP's)

I am trying to create a query that will check the values entered into a

forms combo boxes AGAINST a booking table and check to see if there are any
duplicate entries.

Easy and i've done this bit -

The hard bit is that i need to be able to distinguish between mulitple

hire times for one aircraft within a given date.

ie an aircraft "G-BGIY" on [HireDate] can be booked between the hours of

#09:00:00# and #10:00:00# but also between #12:00:00# and #13:00:00# - how
do i change the following code to allow these two bookings to occur??

--------Code is---------

Private Sub ConfirmBooking_Click()
Dim strSql As String
Dim rstFound As DAO.Recordset

strSql = "Select AcReg FROM BookingDetails where [AcReg] = '" & cboAcReg & "'" _
& " AND ([HireDate] = #" & cboHireDate & "# " _
& " AND [StartTime] >= #" & cboStartTime & "# AND [EndTime] <= #" & cboEndTime & "#) "

Set rstFound = CurrentDb.OpenRecordset(strSql)

If rstFound.RecordCount > 0 Then
Call MsgBox("Sorry this aircraft is already booked, please choose

another time", vbCritical, "Error")

Else
Call MsgBox("Your booking is confirmed", vbExclamation, "Booking Confirmed")
DoCmd.GoToRecord , , acNewRec

End If

End Sub

--------TABLES----------
tbl BookingDetails
AcReg
HireDate
StartTime
EndTIme

------QUERY--------

SELECT BookingDetails.MemberNo, BookingDetails.AcReg,

BookingDetails.HireDate, BookingDetails.StartTime, BookingDetails.EndTime

FROM BookingDetails;

This is the query i want to use the given code to find any duplicates

from - at the moment it seems to get stuck on the HireDate field and will
not allow 2 entries for the same day.

 

[Guest]

Michel

thanks for the reply, i've checked my tables and i have the index for the [HireDate] set at yes (allow duplicates) now, didn't think of that

I'll just change my code to check for overlaps in the [StartTime] and [EndTime] and i should be sorted - as my code stands it should do this - shouldn't it

Cam

 

[Michel Walsh]

HI,



There are two problems.


The first one is around midnight. As example, if you start the
reservation at StartTime=22:00:00 and end if at EndTime=02:00:00 (next day
morning), clearly, it is free from from cboStart=14:00:00 to cboEnd=
18:00:00, but your code will refuse it:

StartTime>cboStart AND EndTime<cboEnd evaluates to
22 > 14 AND 2<18

evaluates to TRUE, and thus, not allow.to reserve the plane for the
afternoon flight. If you would have added the date, the result would have
been false:

(Day)+22:00:00 > (Day)+14:00:00 AND (Day+1)+02:00:00 <
(Day)+18:00:00



The second problem is the statement itself. The correct formulation is:

There is NO overlap, even partial, if, and only if:

cboStart>EndTime OR cboEnd < StartTime


so, there is some overlap, in full or in part, in the negative ( deMorgan
law on the previous statement) :

cboStart < EndTime AND cboEnd > StartTime



Your actual formulation won't find a problem with StartTime=10:00:00 and
EndTIme=13:00:00, while cboStart=11:00:00 and cboEnd=12:00:00, or 14:00:00.


10 > 11 AND 13 < whatever evaluates to FALSE and whatever
evaluates to FALSE, so, don't detect the overlap.


But the proposed formulation will immediatly spot it:

cboStart < EndTime AND cboEnd > StartTime

11 < 13 AND ( later thatn 11) > 10 evaluates properly to TRUE, thus,
detecting the overlap.






Hoping it may help,
Vanderghast, Access MVP

Michel,

thanks for the reply, i've checked my tables and i have the index for the

[HireDate] set at yes (allow duplicates) now, didn't think of that.

I'll just change my code to check for overlaps in the [StartTime] and

[EndTime] and i should be sorted - as my code stands it should do this -
shouldn't it?