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

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

score 12 · Accepted Answer · edited Apr 13 '22 at 17:04

12

Try this:

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

edited Apr 13 '22 at 17:04

June7

19,874
8
24
34

answered Apr 11 '12 at 14:02

Paddy

33,309
15
79
114

sorry, I've made a typo in the question, now it's updated – Totty.js Apr 11 '12 at 14:12

score 1 · Answer 2 · answered Jun 27 '15 at 08:17

1

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:

Like "BB"
(Like "BB")
(Not Like "BB")

answered Jun 27 '15 at 08:17

Ponytell

73
1
7

score 0 · Answer 3 · answered Apr 11 '12 at 14:03

0

Simply restate the target field & condition;

where (field like "*AA*" and field not like "*BB*")

answered Apr 11 '12 at 14:03

Alex K.

171,639
30
264
288

sorry, I've made a typo in the question, now it's updated – Totty.js Apr 11 '12 at 14:13
Do you have other conditions, and if so are the parentheses correct? Past the whole query. – Alex K. Apr 11 '12 at 14:24

score 0 · Answer 4 · answered Apr 11 '12 at 14:21

0

what's the problem with:

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

it should be working.

Could you post some example of your data?

answered Apr 11 '12 at 14:21

Diego

34,802
21
91
134

score 0 · Answer 5 · edited Aug 24 '14 at 10:35

0

Not sure if this is still extant but I'm guessing you need something like

((field Like "AA*") AND (field Not Like "BB*"))

edited Aug 24 '14 at 10:35

Soner Gönül

97,193
102
206
364

answered Aug 24 '14 at 10:31

Roy

1

score 0 · Answer 6 · answered Nov 27 '14 at 23:19

0

If you're doing it in VBA (and not in a query) then: where field like "AA" and field not like "BB" then would not work.

You'd have to use: where field like "AA" and field like "BB" = false then

answered Nov 27 '14 at 23:19

Serge

1

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

6 Answers6