Error 3071

M

Melissa

I have the following query and it gives me the "Too complex" error message:

SELECT tblAll.Doctor, tblAll.ScheduleDate, tblAll.Time, tblAll.Accounts,
tblAll.Review, tblAll.MemberName, tblAll.MemberID, tblAll.FacilityMD,
tblAll.ContactNumber, tblAll.[2ndContact], tblAll.Outcome, tblAll.Situs,
tblAll.Appeal, tblAll.LetterSent, tblAll.ReceivedDate, tblAll.ReceivedTime,
tblAll.DueDate, DateDiff("n",[ScheduledDate] & " " & [Time],Now()) AS
Scheduled
FROM tblAll
WHERE (((tblAll.Doctor)=[Forms]![frmSchedule]![Doctor]) AND
((tblAll.Outcome) Is Null) AND ((DateDiff("n",[ScheduledDate] & " " &
[Time],Now()))>30));

The error only come up when I put a crtieria on the last field. I'm trying
to get a list of every appointment that was over 30 minutes ago. Help!!
 
J

John Spencer

Assuming that ScheduledDate and Time are both DateTime fields, try adding
the time to the date instead of concatenating the two together.

SELECT tblAll.Doctor, tblAll.ScheduleDate, tblAll.Time, tblAll.Accounts,
tblAll.Review, tblAll.MemberName, tblAll.MemberID, tblAll.FacilityMD,
tblAll.ContactNumber, tblAll.[2ndContact], tblAll.Outcome, tblAll.Situs,
tblAll.Appeal, tblAll.LetterSent, tblAll.ReceivedDate, tblAll.ReceivedTime,
tblAll.DueDate, DateDiff("n",[ScheduledDate] & " " & [Time],Now()) AS
Scheduled
FROM tblAll
WHERE (((tblAll.Doctor)=[Forms]![frmSchedule]![Doctor])
AND ((tblAll.Outcome) Is Null)
AND ((DateDiff("n",[ScheduledDate] + [Time],Now()))>30));



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top