访问2013 SQL:“不喜欢"问题 [英] Access 2013 SQL: "IS NOT LIKE" issue

问题描述

我无法弄清楚为什么我的WHERE子句似乎仍然返回值是"SPEC"的列值...我在这里做错了什么? [H1姓氏]是唯一包含此名称/值的列.

I cannot figure out why my WHERE clause seems to still return column values where the value is "SPEC"... what am I doing wrong here? [H1 Last Name] is the only column which contains this designation/value.

SELECT [H1 LAST Name] & ", " & [H1 FIRST Name] AS [FULL Name],
       [H1 E-Mail] AS [E-Mail],
       IIF([H1 Cell Phone] IS NULL, [Home Phone], [H1 Cell Phone]) AS Phone
FROM   NameLookup
WHERE  ((NameLookup.[H1 LAST Name] NOT LIKE '%SPEC%') OR (NameLookup.[H1 LAST Name] NOT LIKE '%MODEL%'))
  AND  (NameLookup.[H1 LAST Name] IS NOT NULL)

UNION ALL

SELECT [H2 LAST Name] & ", " & [H2 FIRST Name] AS [FULL Name],
       [H2 E-Mail] AS [E-Mail],
       IIF([H2 Cell Phone] IS NULL, [Home Phone], [H2 Cell Phone]) AS Phone
FROM   NameLookup
WHERE  ((NameLookup.[H1 LAST Name] NOT LIKE '%SPEC%') OR (NameLookup.[H1 LAST Name] NOT LIKE '%MODEL%'))
  AND  (NameLookup.[H2 LAST Name] IS NOT NULL)

ORDER BY [FULL NAME]

推荐答案

根据查询运行的上下文，LIKE的通配符是不同的.

The wild card characters for LIKE are different depending on the context where the query runs.

在ADO中，通配符为%和_:

From ADO, the wild cards are % and _:

NameLookup.[H1 LAST Name] NOT LIKE '%SPEC%'

在DAO中，通配符为*和?:

From DAO, the wild cards are * and ?:

NameLookup.[H1 LAST Name] NOT LIKE '*SPEC*'

或者您可以使用ALIKE而不是LIKE，然后db引擎将始终期望%和_通配符，而不管查询运行的上下文是什么:

Or you could use ALIKE instead of LIKE, and then the db engine will always expect % and _ wild cards regardless of the context where the query runs:

NameLookup.[H1 LAST Name] NOT ALIKE '%SPEC%'

这篇关于访问2013 SQL:“不喜欢"问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！