Time Query

ddog171

Registered User.
Local time
Today, 05:51
Joined
Jun 27, 2006
Messages
11
I have a table that consists of [employee], [scheduledate], [starttime] and [endtime]. I need to query this table to get the total of all employees working during a certain period of time (ex. 1400 - 2200). I have been pondering this for quite a while and have yet to come up with something.

Any help you can provide would be greatly appreciated.
 
Do you want employees that worked at any time in this range? If so you need to test if [StartTime] is between 14:00 and 22:00 or if [EndTime] is between 14:00 and 22:00. Enter the criteria on separate lines in the query grid to make this an OR criterion.

If you want to capture the times from the user instead of hard coding the times 14:00 and 22:00, capture this in a form to avoid prompting the user twice for the same times.
 
thanks for the quik reply

But... What about people that are on the edge of that time frame......

I want to find out everyone who is working betweeen the hours of 14:00 and 22:00.......


data in tables:

ID/startdates/enddates

1/0600/1400
2/0600/1400
3/1100/1500
4/1200/1600
5/1400/2200
6/1700/2300
7/1800/2100
8/2200/0600

I would need [IDS] 3,4,5,6,7 returned and nothing else. Any more insight on this?

Thanks
 
In the query design grid, when you use the between ... and ... criterion option, it defaults to inclusive (>= AND <=)

In Boolean, you are really saying:

>= 1400 AND <= 2200 in a criterion row for [startdates]
>= 1400 AND <= 2200 in a different criterion row for [enddates]

By putting these on different rows, you get the OR part.

So just drop the 'greater than or equal to' operator >= and use >, so you get

> 1400 AND < 2200 in a criterion row for [startdates]
> 1400 AND < 2200 in a different criterion row for [enddates]
 
OR, just change your edge values to 1401 and 2159!
 
Hi,

Assuming [StartTime] and [EndTime] are date/time fields (i.e. not text fields), given your data, the following query should return [IDS] 3,4,5,6,7:-

PARAMETERS [Enter start time eg 14:00] DateTime, [Enter end time eg 22:00] DateTime;
SELECT ID, StartTime, EndTime
FROM tblData
WHERE IIf([StartTime]>[EndTime],1,[EndTime])>[Enter start time eg 14:00] AND [StartTime]<[Enter end time eg 22:00];


In these kinds of issues, the basic criteria to use is:-
[EndTime] > [RangeStart] AND [StartTime] < [RangeEnd]


The IIF in the criteria takes care of EndTimes that crossed midnight.

The criteria can also return these kinds of records not included in your data:-
StartTime / EndTime
13:00 / 23:00
21:00 / 6:00


If [RangeEnd] may also cross midnight, then you will have to use VBA to test for these "cross-midnight" issues and make necessary adjustments to [EndTime] and [RangeEnd] if needed before you can use the > and < operators. In this scenario, the adjustments are more involved than just changing [EndTime] to 1 as in my query above.
.
 
Thanks

Thanks for all that have offered their insight....Jon K's query provides the results that I am looking for. I do have a question about what is necessarywhen the range crosses midnight (2200-0600)?

What values are needed to pass in to this query or do I also need an additional query for these ranges?

Thanks in advance for all your help!
 
Type/paste this function in a Module. It should be able to handle any range, even one that crosses midnight, though I haven't tested it thoroughly.
Code:
Public Function InRange(StartTime, EndTime, RangeStart, RangeEnd) As Variant
   If IsNull(StartTime) Or IsNull(EndTime) Then
      Exit Function
   End If
   
   Dim blnInRange1 As Boolean
   Dim blnInRange2 As Boolean
   Dim RStart As Date
   Dim REnd As Date
   Dim STime As Date
   Dim ETime As Date
   
   RStart = RangeStart
   REnd = RangeEnd
   STime = StartTime
   ETime = EndTime
   
   If EndTime < StartTime Then
      ETime = ETime + 1
   End If
   If RangeEnd < RangeStart Then
      REnd = REnd + 1
      If StartTime <= RangeEnd Then
         STime = STime + 1
         ETime = ETime + 1
      End If
   End If
  
   If (ETime > RStart And STime < REnd) Then
      blnInRange1 = True
   End If
   If ETime > 1 And RangeStart < RangeEnd Then
      RStart = RStart + 1
      REnd = REnd + 1
      If (ETime > RStart And STime < REnd) Then
         blnInRange2 = True
      End If
   End If
   If blnInRange1 Or blnInRange2 Then
      InRange = True
   End If
End Function


Then you can use the VBA function in a query by passing four arguments to it e.g.

PARAMETERS [Enter start time eg 22:00] DateTime, [Enter end time eg 6:00] DateTime;
SELECT tblData.ID, tblData.StartTime, tblData.EndTime
FROM tblData
WHERE InRange([StartTime], [EndTime], [Enter start time eg 22:00], [Enter end time eg 6:00]);

Hope it helps.
.
 
Last edited:
Thank you

Thanks for all the replies and help, the function works as needed!
 

Users who are viewing this thread

Back
Top Bottom