Query a range of dates

[gol4]

I have a database used to track attendance
The table uses occurstart to hold the date they first missed and occurreturn for the date they returned. Example
empid Occurstart Occurreturn
123 6/1/2001 6/2/2001 all days in range
345 6/29/2001 7/3/2001 2 days in June
234 5/28/2001 6/3/2001 3 days in June
654 5/29/2001 7/1/2001 30 days in June

using SQL how can I capture all Employees that missed days in June

WHERE (((Attendance.occurstart) Between #6/1/01# And #6/30/01#)) OR (((Attendance.occurreturn) Between #6/1/01# And #6/30/01#)); NOPE
Where occurstartdate >= 6/1/01 or occurreturn <6/30/01; NOPE
Can’t seem to reliably query on either field based on a range.
I either get a portion of the records or too many records.
Where occurstart >= 6/1/01 and occurreturn <6/1/01 works
If I loop through each date in the month and compare
I currently am able to get the information using this function

Function DateRange(startdate As String, lastdate As String, Optional monthNumber As String) As String
Dim datcount As Integer, datintv As Integer
startdate = DateValue(startdate)
lastdate = DateValue(lastdate)
datcount = DateDiff(&quot;d&quot;, startdate, lastdate)
For datintv = 0 To datcount - 1
If monthNumber = &quot;&quot; Then 'print whole range
DateRange = DateRange & &quot;, #&quot; & DateAdd(&quot;d&quot;, datintv, startdate) & &quot;#&quot;
Else 'only print in month
If monthNumber = Month(DateAdd(&quot;d&quot;, datintv, startdate)) Then
DateRange = DateRange & &quot;#&quot; & DateAdd(&quot;d&quot;, datintv, startdate) & &quot;#,&quot;
End If
End If
Next datintv
If DateRange = &quot;&quot; Then Exit Function
DateRange = Left(DateRange, Len(DateRange) - 1) 'strip last comma
End Function

I know my issue is not unique, people must deal with this all the time IE.. datein-dateout, datedue-datedone
I would really like know how to get an accurate return using SQL. Any help would be appreciated

 

[Guest_imported]

Whats the syntax for your SELECT statement?

 

[Guest_imported]

I ran a similar query without as many brackets and it worked out, the syntax I think for your query would as follows:

SELECT Attendance.empid, Attendance.occurstart, Attendance.occurend
FROM Attendance
WHERE (((Attendance.occurstart) Between #6/1/01# And #6/30/01# OR (Attendance.occurreturn) Between #6/1/01# And #6/30/01#));

I hope this helps

 

[gol4]

Nice try Phil but
WHERE (((Attendance.occurstart) Between #6/1/01# And #6/30/01# OR (Attendance.occurreturn) Between #6/1/01# And #6/30/01#));

misses the last record because the startdate is not in the range and the return date is not in the range either so neither condition tests true