访问查询中的多个AND [英] Multiple AND in Access Query

问题描述

我的访问查询的行为如此奇怪，应该根据条件选择查询记录，但是这样做不是这样. 查询是

My access query act so strange, the query is supposed to select the record according to the condtion but it is not doing like this. the query is

select Distinct name from table1 where search like '%blue%' order by name

当我运行上述查询时，将返回所有包含蓝色关键字的记录.

when i run the above query all the record are returned containing blue keyword.

如果我在此查询中添加更多单词，

if i add more word to this query,

select Distinct name from table1 where search like '%blue%' And '%red%' order by name

应该选择蓝色和红色的那些记录.但它会返回蓝色或红色的记录.它不适用and. 这是表结构

it is supposed to select those record which are blue and red. but it return the records which are just blue or red. it does not apply the and. this is table structure

id         path              name            search_keyword
1          c:\my picture\    red door           red;
2          c:\my picture\    red door           38;
3          c:\my picture\    red door           wood; 
4          c:\my picture\    red door           2500;
5          c:\my picture\    red door           smooth
6          c:\my picture\    blue door          blue ;
7          c:\my picture\    blue door           38;
8          c:\my picture\    blue door           wood; 
9          c:\my picture\    blue door           2600;
19         c:\my picture\    blue door           smooth;

推荐答案

在Access会话中运行该查询时，请注意所使用的查询模式.您可以在以下链接中找到有关Access的ANSI-89和ANSI-92查询模式的详尽讨论:

Be aware of the query mode you're using when you run that query in an Access session. You can find a thorough discussion of Access' ANSI-89 and ANSI-92 query modes at this link: Comparison of Microsoft Access SQL and ANSI SQL

在另一个堆栈溢出"问题中，您正在使用OleDb从c#运行类似的查询来对数据库进行操作. OleDb表示您的查询将以ANSI-92模式运行，因此%是有效的通配符.

In another of your Stack Overflow questions, you were running a similar query from c# using OleDb to operate with the database. OleDb means your query will run in ANSI-92 mode, so % is the valid wildcard character.

但是，对于在Access会话中运行的查询，默认模式为ANSI-89，这意味着对应的通配符为*而不是%.

However, for a query run in an Access session, the default mode is ANSI-89, which means the corresponding wildcard is * instead of %.

SELECT DISTINCT [name]
FROM table1
WHERE search_keyword Like '*blue*'
ORDER BY [name]

如果已通过工具"->选项"->表/查询"选项卡配置了访问，则为""此数据库" 选择""SQL Server兼容语法(ANSI 92)" >，您的查询将使用ANSI-92通配符.

If you have configured Access by Tools->Options->Tables/Queries tab, then selected "SQL Server Compatible Syntax (ANSI 92)" for "This Database", your query will expect the ANSI-92 wildcards.

SELECT DISTINCT [name]
FROM table1
WHERE search_keyword Like '%blue%'
ORDER BY [name]

另一个选择是使用ALike而不是Like比较运算符. ALike指示数据库引擎期望ANSI-92通配符，而不管查询在何处以及如何运行.这样，您可以在Access会话中使用ANSI-92通配符，而无需设置我上面描述的选项.

Another option is to use the ALike instead of Like comparison operator. ALike signal the database engine to expect ANSI-92 wildcards regardless of where and how the query is run. That way you can use the ANSI-92 wildcard within an Access session without setting the option I described above.

SELECT DISTINCT [name]
FROM table1
WHERE search_keyword ALike '%blue%'
ORDER BY [name]

这篇关于访问查询中的多个AND的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！