（不等于）不适用于简单的访问查询( (not equal) is not working in a simple access query)

<>（不等于）不适用于简单的访问查询(<> (not equal) is not working in a simple access query)

我有以下查询，我想将所有活动员工的CardStatus <>“丢失卡”放在哪里。

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"));

如果我将<>“丢失的卡”替换为“丢失的卡”

如果我将<>“Lost Card”替换为null，它会起作用（这会给出我期待的结果）

如果我将<>“丢失的卡片”替换为不为null，它将起作用。

无论出于何种原因，它不喜欢“<>”。 是的我知道我可以使用“is null”方案来获得相同的结果，我很好奇为什么<>不起作用。 如果重要的话查询从链接的ODBC连接拉到sql server。

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.

最满意答案

将Null与任何内容进行比较将只返回Null：

?Null<>"Lost Card" Null ?Null="Lost Card" Null

您需要在条件中明确检查Null：

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

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)