<> (not equal) is not working in a simple access query

I have the following query where I want to pull all active employees where their CardStatus <> "Lost Card".

SELECT
    dbo_Employee.Status, 
    dbo_EmpBadgeNumbers.EmployeeID_FK, 
    dbo_EmpBadgeNumbers.CardID, 
    dbo_EmpBadgeNumbers.CardStatus 
FROM dbo_Employee INNER JOIN dbo_EmpBadgeNumbers 
        ON dbo_Employee.EmployeeID = dbo_EmpBadgeNumbers.EmployeeID_FK 
WHERE (((dbo_Employee.Status) = "Active") 
    AND ((dbo_EmpBadgeNumbers.CardStatus) <> "Lost Card")); 

If I replace the <> "Lost Card" with "Lost Card" it works,

If I replace the <> "Lost Card" with is null it works (which gives the result I'm looking for)

If I replace the <> "Lost Card" with is not null it works.

For whatever reason it doesn't like the "<>". Yes I know I can just use the "is null" scenario to get the same result, I'm curious as to why the <> is not working. If it matters the query is pulling 开发者_C百科from a linked ODBC connection to sql server.

---

Comparing Null to anything will simply return Null:

?Null<>"Lost Card"
Null

?Null="Lost Card"
Null

You need to do an explicit check for Null in your criteria:

WHERE (CardStatus <> "Lost Card" Or CardStatus Is Null)

---

<> never does work with Null.

Incorrect: WHERE CardStatus <> Null
Correct: WHERE CardStatus Is Not Null

---

I assume you have two values in CardStatus: "LostCard" and null (empty)

Basically, when you do ANY comparison against null the result is always null and is always a failure. This is the reason why isNull was created.

---

I would then try

CardStatus is null
OR NOT ( CardStatus = "Lost Card" )

The original NOT CardStatus = "Lost Card" would NOT equate as

(NOT CardStatus) = "Lost Card"

as its base type is that of character so would be compared based on the "=", then apply the logical NOT... such as

NOT (CardStatus = "Lost Card")