Exclude LATE but include empty

[dhborchardt]

In this query there is a field [Qualifiers] that only gets used when there is an edit made. If an edit has not been made this field will be empty. If an edit is made then this field will contain either LATE or <FIXED>. I want to ignore all records where this field = LATE. I tried using - NOT "LATE" - in the criteria and it will eliminate all records that contain "LATE" but it also leaves out all the empty records and only shows the records that have <FIXED> entered. How can I eliminate all records that contain "LATE" and show all other records including the ones that contain <FIXED>.


SELECT dbo_AdmVisits.VisitID, dbo_AdmVisits.Name, dbo_AdmVisits.PatientID, dbo_AdmVisits.InpatientServiceID, dbo_AdmVisits.InpatientServiceName, dbo_AdmVisits.InpatientOrOutpatient, dbo_AdmittingData.AdmitDateTime, dbo_AdmDischarge.DischargeDateTime, dbo_AdmVisitEvents.Code, dbo_AdmVisitEvents.EventActualDateTime, dbo_AdmVisitEvents.Qualifiers
FROM dbo_AdmVisitEvents RIGHT JOIN ((dbo_AdmVisits INNER JOIN dbo_AdmittingData ON dbo_AdmVisits.VisitID = dbo_AdmittingData.VisitID) INNER JOIN dbo_AdmDischarge ON dbo_AdmittingData.VisitID = dbo_AdmDischarge.VisitID) ON dbo_AdmVisitEvents.VisitID = dbo_AdmVisits.VisitID


WHERE (((dbo_AdmVisitEvents.Code)="ENADMIN" Or (dbo_AdmVisitEvents.Code)="ENADMOB") AND ((dbo_AdmVisitEvents.EventActualDateTime)>#3/28/2011#) AND (Not (dbo_AdmVisitEvents.Qualifiers)="LATE"))
ORDER BY dbo_AdmVisitEvents.EventActualDateTime;

[kennejd]

You might try the below conditions...I used an isnull function to include the null matches...and I included a check for empties:

WHERE
(dbo_AdmVisitEvents.Code="ENADMIN" Or dbo_AdmVisitEvents.Code="ENADMOB")
AND dbo_AdmVisitEvents.EventActualDateTime>#3/28/2011#
AND (dbo_AdmVisitEvents.Qualifiers <> "LATE" or isnull(dbo_AdmVisitEvents.Qualifiers) = true or dbo_AdmVisitEvents.Qualifiers = "")

[dhborchardt]

That's works thanks