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

  • Thread starter Thread starter Keenan
  • Start date Start date
K

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.
 
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#
Click to expand...

(That's without the dates of course.)
 
Thanks, I can't beleive it was something as simple as that! Appreciate it!

Allen Browne said:
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#
Click to expand...

(That's without the dates of course.)

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

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

Keenan said:
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.
Click to expand...
Click to expand...
 
Back
Top