I am tryign to write a query that will show me telephone calls made with in a given time period.
In my Calls table I have
Phone#, TimeCallMade, DateCallMade

In my Schedule table I have
DateofShift, StartTime, EndTime

I linked the DateofShift to the DateCallMade and that gives me all the calls made on that shift date. Now I need to determine the calls made 15 mins before and after the StartTime of the shift and 15 minutes before and after the EndTime of the shift.

In my query builder I have listed the DateofShift, Phone#, TimeCallMade and I figure I need to put the criteria under TimeCallMade but I can't figure the best way to write it to get the results I want.

Any suggestions?

Try this, I am assuming TimeCallMade and DateCallMade are datetime datatypes.

Select *
From YourTable
Where TimeCallMade + DateCallMade Between DateAdd("n" -15, TimeCallMade + DateCallMade) and DateAdd("n" 15, TimeCallMade + DateCallMade)

Thanks, I see what you are doing by adding them together but I am not sure how that will get me the desired result.

I need to see all the calls made by shiftdate within 15min+/- of the shift start time and 15min+/- of the shift end time.
If I read your criteria correctly it tells me to select all records from calls table where time call made is between the 15 minutes added to and subtracted from the time call made. Did you perhaps mean to indicate shift start time here instead of TimeCallMade ?

ie:
Select *
From Calls
Where Calls!TimeCallMade + Calls!DateCallMade Between DateAdd("n" -15, Schedule!StartTime + Schedule!SateofShift) and DateAdd("n" 15, Schedule!StartTime + Schedule!SateofShift)

Also, I would need to do the query twice no? Once for the start of shift and once for the end time of shift? (as written above)

(ps. not sure if the use of ! is correct... trying to indicate the table it is from)

Try something like this:

Select *
From tblCalls INNER JOIN tblSchedule ON tblCalls.DateCallMade = tblSchedule.DateofShift
Where Abs(DateDiff("n",tblCall.TimeCallMade,tblSchedule.StartTime)) <= 15 And Abs(DateDiff("n",tblCalls.TimeCallMade,tblSchedule.EndTime)) <=15

... Basically I am taking the absolute value of the difference between the times in question and comparing them to 15. (note: AIR CODE!!!!)

Sorry ... a slight correction is needed:



(previously, I had an "And" instead of an "Or" ... with the "And" no records will be returned...)

your solution works perfectly. Thank you very much.
Now, say instead of wanting all the calls +/- 15min I wanted all the calles between the start and end time. Would I use the criteria ">starttime or <endtime"? or could I use between?

... Or ...