Query – several criteria (Access 2003)

Topic

New Reply

In the Query below I want to ask two questions in the Field “ReviewDue”. I am looking for the dates overdue from today AND where this field is blank and has no date entered at all.

Can anyone help me?

SELECT tbl_Applications.Surname, tbl_Applications.StaffName, tbl_Applications.Given, tbl_Applications.DCSID, tbl_Applications.Status, tbl_Applications.ProjectID, tbl_Applications.FMDNO, tbl_Applications.TransmitterNo, lup_Criteria.Criteria, tbl_Applications.Institution, tbl_Applications.ReviewNotes, tbl_Applications.ReviewDue, tbl_Applications.ElectronicsClause, tbl_Applications.TypeHD, tbl_Applications.StartDates, tbl_Applications.EndDates, tbl_Applications.SupervisionStandard
FROM tbl_Staff RIGHT JOIN (lup_Criteria INNER JOIN tbl_Applications ON lup_Criteria.CriteriaID = tbl_Applications.CriteriaID) ON tbl_Staff.StaffID = tbl_Applications.ReportWriter
WHERE (((tbl_Applications.Status)=”active”) AND ((lup_Criteria.Criteria)=”caseload” Or (lup_Criteria.Criteria)=”AWOL/In Custody/UAL”) AND ((tbl_Applications.ReviewDue)<Now()) AND ((tbl_Applications.Region)="WMR")) OR (((tbl_Applications.Region)="NMR"));

Reply | Quote

Replies

Try this version:

SELECT tbl_Applications.Surname, tbl_Applications.StaffName, tbl_Applications.Given, tbl_Applications.DCSID, tbl_Applications.Status, tbl_Applications.ProjectID, tbl_Applications.FMDNO, tbl_Applications.TransmitterNo, lup_Criteria.Criteria, tbl_Applications.Institution, tbl_Applications.ReviewNotes, tbl_Applications.ReviewDue, tbl_Applications.ElectronicsClause, tbl_Applications.TypeHD, tbl_Applications.StartDates, tbl_Applications.EndDates, tbl_Applications.SupervisionStandard
FROM tbl_Staff RIGHT JOIN (lup_Criteria INNER JOIN tbl_Applications ON lup_Criteria.CriteriaID = tbl_Applications.CriteriaID) ON tbl_Staff.StaffID = tbl_Applications.ReportWriter
WHERE (((tbl_Applications.Status)=”active”) AND ((lup_Criteria.Criteria)=”caseload” OR (lup_Criteria.Criteria)=”AWOL/In Custody/UAL”) AND (((tbl_Applications.ReviewDue)<Now()) OR ((tbl_Applications.ReviewDue) Is Null)) AND ((tbl_Applications.Region)="WMR")) OR (((tbl_Applications.Region)="NMR"));

Reply | Quote

Duh! I feel like a right twit! I tried the is null stuff but not Or. Must be too late in the day for my brain.

Thanks Hans.

Reply | Quote

It’s still early here!

Reply | Quote