WHERE clause "too complex"

[Ted C]

I'm trying to do a query that uses a date range, but Access doesn't like it.

SELECT tblClass.ClassName, 
           tblClassSession.SessionDate, 
           [LastName] & ", " & [FirstName] AS StudentName, 
           tblAttendanceStatus.Description AS Status
FROM tblPersonnel INNER JOIN (tblClass INNER JOIN (tblClassSession INNER JOIN (tblAttendanceStatus INNER JOIN tblClassAttendance ON tblAttendanceStatus.StatusID = tblClassAttendance.StatusID) ON tblClassSession.ClassEventID = tblClassAttendance.ClassEventID) ON tblClass.ClassID = tblClassSession.ClassID) ON tblPersonnel.StudentID = tblClassAttendance.StudentID
WHERE (tblClassSession.SessionDate) >= CDate([Forms]![frmReportsMenu]![txtStartDate])
   AND (tblClassSession.SessionDate) >= CDate([Forms]![frmReportsMenu]![txtEndDate])
ORDER BY tblClass.ClassName, tblClassSession.SessionDate, [LastName] & ", " & [FirstName];

If I take out

AND (tblClassSession.SessionDate) >= CDate([Forms]![frmReportsMenu]![txtEndDate])

the query works.

What about that little "AND" clause makes this query impossible to execute?

Incidentally, txtStartDate and txtEndDate on the frmReportsMenu form are text fields with a Short Date input mask.

[pbaldy]

Not sure offhand, but two thoughts come to mind. First, I suspect you want "<" (less than) rather than ">" for that second criteria. Second, you may not need the CDate() function. I don't normally use it in this type of situation, and perhaps it's causing the complexity.

[fishbase]

have you tried

WHERE
(tblClassSession.SessionDate) BETWEEN
CDate([Forms]![frmReportsMenu]![txtStartDate])
AND CDate([Forms]![frmReportsMenu]![txtEndDate])

[Ted C]

fishbase... I tried a BETWEEN statement, and I was getting the same result.

pbaldy... looks like I was missing the obvious, as usual. Changing the sign fixed it.

[pbaldy]

Glad you got it sorted out (though I'm surprised Between didn't work).