How to use "like" and "not like" in SQL MSAccess for the same field? [closed]

Question

I would like to filter all the rows that are:

field like "*AA*" and field not like "*BB*"

But this is returning everything instead of showing all the rows that contains AA and doesn't contain BB.

well now it works like expected, just restarted ms access...

Sorry for my typo... :s updated

Answer 1

Try this:

field like "*AA*" and field not like "*BB*"

Answer 2

What I found out is that MS Access will reject --Not Like "BB*"-- if not enclosed in PARENTHESES, unlike --Like "BB*"-- which is ok without parentheses.

I tested these on MS Access 2010 and are all valid:

1. Like "BB"

2. (Like "BB")

3. (Not Like "BB")