Time frame query (Access 2000 SR1a)

Topic

New Reply

Is there a way to have a start time and end time field. But create a query that will see if a time is used or not. Example, if Room A is booked from 1 to 3 and user requests the room at 2 it will let them know Room A is booked. Or do i have to create a record for every increment a room is booked to check if it is available.

Thanks
Tom

Reply | Quote

Replies

You could build a query that looks for rooms where the booking start time is less than the requested time, and the booking finish time is greater than the requested time. Any rooms returned will be booked at the requested time.

Reply | Quote

John,

I tried your suggestion and didn’t get the results expected. However, I started experimenting a little bit and found that if I make the requested start time >= the current start time and the requested end time <= the current booked finish time I did get the desired results. The query now returns rooms already booked during that time frame.

Thank you for helping me over this stumbling block.
It really got the creative juices flowing.

Tom

Reply | Quote

You can also use the Between [Date1] And [Date2] syntax for the same purpose. That includes both the beginning and ending values in the returned records. However, if you are using general dates (i.e., dates entered using the Now() function), you need to be careful because a date entered without the time specified won’t match the dates you expect. In that case, you have to specify less than the next day as the second criteria in order to include all date/times up to midnight.

Reply | Quote