Query does not exclude the records with "Exclusion" on it.

[101287]

Can someone please help me with the query below. I am trying to exclude the records that have "Exclusion" on a field and the query is not excluding them. Not sure what is the problem with the query. Guidance please.

SELECT tblDNSaudit.DNSName, tblDNSaudit.IP, tblAuditIdDescription.Category, tblDNSaudit.InitialDate AS DateIdentified, tblDNSaudit.AppearDate AS DateLastReported, tblDNSaudit.AuditId, tblAuditIdDescription.Description, tblAuditIdDescription.FixInformation, tblDNSaudit.ExclusionCategory, IIf([Category]="Category III",[DateIdentified]+180,IIf([Category]="Category II",[DateIdentified]+60,IIf([Category]="Category I",[DateIdentified]+25,"F"))) AS CatDueDate
FROM tblAuditIdDescription INNER JOIN tblDNSaudit ON tblAuditIdDescription.AuditID = tblDNSaudit.AuditId
WHERE (((tblAuditIdDescription.Category)<>"Category IV") AND ((tblDNSaudit.AppearDate)=(SELECT Max(tblDNSaudit.AppearDate) AS MaxOfAppearDate
FROM tblDNSaudit)) AND ((tblDNSaudit.ExclusionCategory)<>"Exclusion"))
ORDER BY tblDNSaudit.DNSName, tblAuditIdDescription.Category;


Thank you, Luis

 

[dhookom]

You have the AND ((tblDNSaudit.ExclusionCategory)<>"Exclusion")) in the FROM clause rather than the WHERE clause.

Duane
Hook'D on Access
MS Access MVP

 

[101287]

The from that you mentioned is for the sub-query not the primary query. However, thank you for feedback.

 

[dhookom]

Sorry about that.

Are you sure the value is "Exclusion"?

Did you create a select query with ="Exclusion"?

Duane
Hook'D on Access
MS Access MVP

 

[101287]

Yes I'm sure the value is "Exclusion". Yes, I created a query equal to "Exclusion" and it displays all the record with exclusion. Then if I change the = "Exclusion" to <> "Exclusion" then I don't get nothing on the query.

I'm really confused. Thanks, Luis

 

[PHV]

Seems like a NULL issue.
What about this criria ?
Nz(tblDNSaudit.ExclusionCategory, "?")<>"Exclusion"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[101287]

PHV - it helps. How did you figure out that was a Null issue.

 

[PHV]

Your statement:
if I change the = "Exclusion" to <> "Exclusion" then I don't get nothing on the query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

... plus – experience.
If it happens to you a few times, finally you are able to spot the issue right away. If it happens to somebody else, you can see it clearly way before they can figure it out.

Am I right, PHV?


Have fun.

---- Andy