CODEWITHSUNDEEP
ms-access
safi
safi

Reputation: 3766

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

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;

Upvotes: 0

Views: 122

Answers (2)

HansUp
HansUp

Reputation: 97101

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

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.

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]

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]

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]

Upvotes: 2

Rasel
Rasel

Reputation: 15477

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

Upvotes: 0

Related Questions