Group Date & Time values Between two fields (room timetabling)

[Keenan]

Query with fields/data:
Room - Date - Start time - End time
123 - 27/08/08 - 06:00pm - 09:00pm
124 - 27/8/08 - 07:00pm - 10:00pm
125 - 27/8/08 - 07:30pm - 08:00pm

I want to know which rooms are being utilised between 07:30pm - 08:00pm, ie
the results should show all 3 rooms are being used. However if I use the
Between And function it only will show room 125.

Thanks in advance.

 

[Allen Browne]

The room is being used for at least part of the time between 7:30 and 8:00
if both these are true:
- the booking starts before 8 pm, and
- the booking finishes after 7:30pm.

Therefore you want this in the Criteria row under the [End Time] field:
< #20:00:00#
and this under the [Start Time] field:

#19:30:00#

(That's without the dates of course.)

 

[Keenan]

Thanks, I can't beleive it was something as simple as that! Appreciate it!

The room is being used for at least part of the time between 7:30 and 8:00
if both these are true:
- the booking starts before 8 pm, and
- the booking finishes after 7:30pm.

Therefore you want this in the Criteria row under the [End Time] field:
< #20:00:00#
and this under the [Start Time] field:

#19:30:00#

(That's without the dates of course.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Query with fields/data:
Room - Date - Start time - End time
123 - 27/08/08 - 06:00pm - 09:00pm
124 - 27/8/08 - 07:00pm - 10:00pm
125 - 27/8/08 - 07:30pm - 08:00pm

I want to know which rooms are being utilised between 07:30pm - 08:00pm,
ie
the results should show all 3 rooms are being used. However if I use the
Between And function it only will show room 125.

Thanks in advance.