CODEWITHSUNDEEP
sqlms-accessms-access-2007ms-access-2003
Matt Facer
Matt Facer

Reputation: 3105

ms access 2007 SQL "not like" is ignoring blank cells

I'm confused by some SQL I am trying to run. Here's a sample dataset

Username ID        Text1
red55   1235342     fdfdf
black   5542121     sdsd
red32   5542145     sffrds
green   5542911         
bluee   5333121     ffds

So the SQL would be 

SELECT username, ID, Text1 
  FROM user_info
 WHERE username NOT LIKE 'red*' 
       AND text1 NOT LIKE 'sd*';

So I would expect to see a result set of

Username ID        Text1
green   5542911         
bluee   5333121     ffds

But it doesn't work. It seems to ignore the blank values. I would only see the one record (not like the two above). I need my SQL to simply say show me the records which dont start "red" AND also any records which don't start with "sd". Yet it either gets rid of them all AND blanks, or if I use an OR statement, gets rid of none.

Any ideas? I am stuck here!

Upvotes: 1

Views: 4570

Answers (1)

mwolfe02
mwolfe02

Reputation: 24207

You need to explicitly handle the nulls. One option would be:

SELECT username, ID, Text1 
FROM user_info 
WHERE (username Is Null OR username NOT LIKE 'red*')
  AND (text1 Is Null OR text1 NOT LIKE 'sd*')

Upvotes: 5

Related Questions