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

Question

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 from a linked ODBC connection to sql server.

Answer 1

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)

Answer 2

<> never does work with Null.

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