Criteria "<>" is not working

[Locopete99]

Hi All,

I have a query with 4 "or" criteria.

The problem is the assigned to values are ignoring the "<>" criteria that I've put in.

I've attached a pic of the design view and the SQL code below.

INSERT INTO tbl_ISDreplies ( ID, [Part Number], [User] )
SELECT Tbl_ReqType.ID, Tbl_ReqType.[Part Number], Tbl_ReqType.User
FROM Tbl_ReqType
WHERE ((Not (Tbl_ReqType.[New Completetion Date]) Is Null) AND ((Tbl_ReqType.Complete)=False) AND ((Tbl_ReqType.[Assigned To])<>"Customer Care")) OR ((Not (Tbl_ReqType.[New Completetion Date]) Is Null) AND ((Tbl_ReqType.Complete)=False) AND ((Tbl_ReqType.[Assigned To])<>"Production Control")) OR ((Not (Tbl_ReqType.[New Completetion Date]) Is Null) AND ((Tbl_ReqType.Complete)=False) AND ((Tbl_ReqType.[Assigned To])<>"Logistics"));

Can someone let me know why this is being ignored as I need to make sure it starts following the criteria.

 

[plog]

It's not ignoring it, you just have used ORs where you need ANDs. All your Assigned criteria needs to be on the same row, not on different rows. The way you have it now it evaluates like this:

(AssignedTo<>"Customer Care" OR AssignedTo<>"Production" OR AssignedTo<>"Logistics")

When you use OR, if any one of the individual criterion is TRUE, the whole thing is TRUE. That means if AssignedTo="Logistics" your statement is TRUE because the above gets evaluated as so:

TRUE OR TRUE OR FALSE

Right? Since AssignedTo equals Logistics it does not equal Customer Care nor Production. Those 2 criteria become TRUE and thus the whole thing becomes TRUE. Your criteria for AssignedTo needs to be this:

(AssignedTo<>"Customer Care" AND AssignedTo<>"Production" AND AssignedTo<>"Logistics")

AND makes everything be TRUE for the whole thing to be TRUE--one FALSE in the bunch and the whole thing is FALSE.

 

[sneuberg]

. Your criteria for AssignedTo needs to be this:

(AssignedTo<>"Customer Care" AND AssignedTo<>"Production" AND AssignedTo<>"Logistics")

or by De Morgan law:


Not((AssignedTo="Customer Care" Or AssignedTo="Production" Or AssignedTo="Logistics")

Is another way of looking at it.

 

[Galaxiom]

Not((AssignedTo="Customer Care" Or AssignedTo="Production" Or AssignedTo="Logistics")

Or more concisely:

AssignedTo Not IN("Customer Care", "Production", "Logistics")

However I would store the AssignedTo values as numbers rather than strings. Then there is potential for expressions like these:

AssignedTo Not IN(1,2,3)

AssignedTo > 3

Either is far more efficient to process than matching strings.

The strings they represent can be retrieved from a lookup table when they need to be displayed.

 

[Locopete99]

Thanks guys, feel stupid now lol

 

[The_Doc_Man]

Naw, pete -don't feel stupid. You came here for help, got it, and learned something. Feel exhilarated at having learned a little more about how to look at problems.