Criteria syntax question

[splint1906]

I have a table with Vendor#, VendorName and Status. The Status field has either OBS (Obsolete), NA (not available) or is blank.

I am trying to write a query that displays all Vendor# when Status field does not equal OBS or NA but, I can't get it to work right. My problem is the WHERE statement. I'm not sure if <>"OBS" is the right syntax.

The SQL is:
SELECT Vendors.VendorCode, Vendors.VendorName, Vendors.VendorUnion
FROM Vendors
WHERE (((Vendors.VendorUnion)<>"OBS" Or (Vendors.VendorUnion)<>"NA"))
ORDER BY Vendors.VendorCode;

 

[hneal98]

SELECT Vendors.VendorCode, Vendors.VendorName, Vendors.VendorUnion
FROM Vendors
WHERE (Vendors.VendorUnion not in("OBS","NA"))
ORDER BY Vendors.VendorCode;

Not sure if this is what you want.

 

[splint1906]

That's it. Thanks

 

[hneal98]

No problem.

 

[JonFer]

splint1906 - You needed to use AND instead of OR for your syntax:

WHERE (((Vendors.VendorUnion)<>"OBS" AND (Vendors.VendorUnion)<>"NA"))

The opposite of "a=b or a=c" (you want records where a equals b or c) is "a<>b AND a<>c" (you want records where a is neither b nor c).

"a<>b OR a<>c" is always TRUE (unless NULLS are involved or b=c).

John

http://www.interautomateconsulting.com