Query in Access using NOT LIKE

Question

I am using a query as follows.

SELECT * FROM TableName
WHERE ([Material Description] Not Like "*LICENSE*" Or 
       [Material Description] Not Like "*LICENCE*");

However, this fetches me results having records with LICENCE or LICENSE in the Material Description field. Please advise as to what is that I am missing so that my query yields me result omitting the records containing these words in the Material Description field.

Answer 1

What you actually want is AND in between:

SELECT * FROM TableName
WHERE ([Material Description] NOT LIKE '*LICENSE*' AND 
       [Material Description] NOT LIKE '*LICENCE*');

You will currently select the record "LICENSE" because it does NOT contain "LICENCE" and the record "LICENCE" because it does NOT contain "LICENSE".
The records you currently really exclude are the ones which contain "LICENCE" AND "LICENSE", probably not many ;). That little confusion arises from the usage of Or in combination with Not.

Another way to achieve the same goal would be to move the NOT in front of the ORed condition:

SELECT * FROM TableName
WHERE NOT ([Material Description] LIKE '*LICENSE*' OR
           [Material Description] LIKE '*LICENCE*');

That way it is a little bit clearer what you actually want to achieve.
This will read NOT (A OR B) as opposed to (NOT A) OR (NOT B) and has very different truth table:

A  B  |  (  ~  A  )  &  (  ~  B  )
----------------------------------
0  0  |     1  0     1     1  0     
0  1  |     1  0     0     0  1     
1  0  |     0  1     0     1  0     
1  1  |     0  1     0     0  1 


A  B  |  ~  (  A  |  B  )
-------------------------
0  0  |  1     0  0  0     
0  1  |  0     0  1  1     
1  0  |  0     1  1  0     
1  1  |  0     1  1  1  

Your truth table would look like

A  B  |  (  ~  A  )  |  (  ~  B  )
----------------------------------
0  0  |     1  0     1     1  0     
0  1  |     1  0     1     0  1     
1  0  |     0  1     1     1  0    
1  1  |     0  1     0     0  1    

Answer 2

Without the wildcard character (* in MS Access, % in most reasonable databases), like behaves like the = operator. If you want to exclude records where these words are contained somewhere in the relevant field, just surround it with wildcards:

SELECT * 
FROM   TableName 
WHERE  ([Material Description] NOT LIKE '*LICENSE*' OR 
        [Material Description] NOT LIKE '*LICENCE*');

Answer 3

You can do what you need with a single Like pattern which includes a character range:

SELECT * FROM TableName
WHERE [Material Description] Not Like '*LICEN[CS]E*';

That means exclude rows whose [Material Description] values contain LICEN, followed by either C or S, followed by E. At least to me, its meaning is clearer than the combination of 2 Not Like conditions.

It would be even simpler if you can broaden the scope to LICEN, followed by any single character, followed by E. It that is suitable, use ? to represent any single character:

WHERE [Material Description] Not Like '*LICEN?E*';

Answer 4

You should check your conditions.

• The first condition of the statements gets all records with [Material Description] doesn't contains "LICENSE". So I gives you also the records witch contains "LICENCE".
• The second condition gives you all records with [Material Description] doesn't contains "LICENCE". So you'll get records with "LICENSE" too.
• Because you use an OR-operation the records from both conditions will be combined.

The result is that you get all the records from your table.