Bookings For Vehicles

[databasedonr]

Hi Folks, and I apologise -- there have been a plethora of posts on this topic, most of which I have read, but I still have a question:

I have a database where I want to track vehicle use, and I have 6 vehicles that can be used for any of 15 people. When a person wants a vehicle, they go to the database, enter the dates they want, and then choose the vehicle from a combo box.

I have set the source of the combobox using this query:

strSelVehicle = "SELECT * FROM tblVehicle WHERE VehicleID Not In " & _
"(SELECT VehicleID FROM tblVehicleBook WHERE (DateOut=#" & _
strDateOut & "# OR DateOut Between #" & strDateOut & "# AND #" & strDateIn & _
"#) OR ((DateIn=#" & strDateIn & "#) OR (DateIn Between #" & strDateOut & "# AND #" & strDateIn & "#));"

strDateOut = The date entered on the form for when the vehicle is required
strDateIn = the date entered on the form for when the vehicle will be returned.

DateOut = in the table, the date for which the vehicle is booked
DateIn = in the table, the date for which the vehicle is/will be returned.

The idea here is to only show the vehicles that are available. And it almost works -- except that if I book a Vehicle out on 10-Oct returning 17-Oct, a subsequent request for available vehicles for 12 - 14 Oct shows that the booked out vehicle is available.

So, I have a tblVehicle wherein my cars are listed, and a tblVehicleBook, wherein all my bookings are entered.

I've seen Allen Browne's excellent article on finding duplicate bookings, but I can't seem to translate it to the source of my combobox, likely because I am thick. I know that I somehow need to search the database for where my strDateOut is between my DateOut and DateIn, but don't know how to write the query.
Any suggestions would be most welcome! And thanks in advance!

 

[Jon K]

On systems using US date format, you can use:-

  strSelVehicle = "SELECT * FROM tblVehicle WHERE VehicleID Not In " & _
        "(SELECT VehicleID FROM tblVehicleBook WHERE DateOut<=#" & _
          strDateIn & "# AND DateIn >= #" & strDateOut & "#)"

On system using non-US date format, you can use the Format() function to convert the dates to US format:-

  strSelVehicle = "SELECT * FROM tblVehicle WHERE VehicleID Not In " & _
        "(SELECT VehicleID FROM tblVehicleBook WHERE DateOut<=#" & _
          Format(strDateIn, "m/d/yyyy") & "# AND DateIn >= #" & _
          Format(strDateOut, "m/d/yyyy") & "#)"

 

[databasedonr]

Thanks, Jon K - that works a treat! I appreciate it.

Sorry to add one more posting to this topic, but it works really well (now); when users request a vehicle, I have them select a date range first, and then only the vehicles that are not booked appear in the combobox.