SQL query date between for booking dates

[Greydaddy]

I am trying to select arrival dates and departure date to find a match between alredy booked dates from a database.
The query is this:
Select Arrival, Departure from RoomBooking where
Arrival between vararrival and vardeparture OR
Departure Between vararrival and vardeparture AND vardeparture Between Arrival and Departure OR
vararrival Between Arrival and Departure AND
RoomNumber = varrmnumber
This seems to pick up any room within the criteria and not the filter for RoomNumber.

if anyone can spot the error i would be very pleased!
I can't see the wood for the trees, is one expression that comes to mind.
Cheers!

 

[PaulBricker]

Start with the Room number
Where RoomNumber = varrmnumber And ...


Paul

 

[Greydaddy]

Hi Paul,
Thanks for your help, but no joy.
I have have filtered each BETWEEN for the room number and it works, seems abit long winded but is all in one query as follows.
Set RsDatecheck = conn.execute("Select RoomNumber, Arrival, RoomAvailable From CustomerRoomBooking where " _
& "#" & vararrival & "# Between Arrival and RoomAvailable AND " _
& "RoomNumber = "& varrmnumber &" OR " _
& "#" & vardeparture & "# Between Arrival and RoomAvailable AND " _
& "RoomNumber = "& varrmnumber &" OR " _
& "Arrival Between #" & vararrival & "# and #" & vardeparture & "# AND " _
& "RoomNumber = "& varrmnumber &" OR " _
& "RoomAvailable Between #" & vararrival & "# and #" & vardeparture & "# AND " _
& "RoomNumber = "& varrmnumber &" Order by RoomNumber&quot

Works for me.

Enjoy programming,
Dave

 

[williamu]

Hi,

Paul is right here. What you need to do is wrap the WHERE predicate criterion with () to provide a level of precedence to the query.

SELECT field, field FROM table
WHERE ((RoomNo = ?) AND (Cond1 ...) AND (cond2 ...))

HTH

--
William
Software Engineer
ICQ No. 56047340

 

[PaulBricker]

Looks good to me. Seems you have to include the room number in each set of criteria. Could be worse.

Paul

The key to Computer Technology is finding the right wrench to hammer in the correct screw.

 

[Greydaddy]

yes, you are both right!
Many thanks william for reminding me of enclosing with ( ), mmmmm simple things eh
Cheers both,
Dave