Using Not Like in a query

[Damon Heron]

I have a query for a report-
Select tblList.ListID, tblList.MemberName, tblList.Notes where
((tblList.Notes) not like "Retired");
I get no results.
If I run -
Select tblList.ListID, tblList.MemberName, tblList.Notes where
((tblList.Notes) like "Retired");

I get the expected list of records with "retired" in the Notes field.
There may be other text in the Notes field, or it may be blank. It is not a
required field and it is set to allow zero length. So how do I exclude the
retired from my query??

Thanks in advance.

 

[Marshall Barton]

I have a query for a report-
Select tblList.ListID, tblList.MemberName, tblList.Notes where
((tblList.Notes) not like "Retired");
I get no results.
If I run -
Select tblList.ListID, tblList.MemberName, tblList.Notes where
((tblList.Notes) like "Retired");

I get the expected list of records with "retired" in the Notes field.
There may be other text in the Notes field, or it may be blank. It is not a
required field and it is set to allow zero length. So how do I exclude the
retired from my query??

Like must match the entire field, but it is design to do
what you want when you tell it what you want.

In your comparison, you might as well be using <> because
you have not used any wildcards in the pattern. Try using:

WHERE tblList.Notes Not Like "*Retired*"

Look up wildcard in Help for all the details, but the first
asterick tells it to match anything before the word Retired
and the second asterick matches anything after it.

 

[Damon Heron]

Sorry, Marshall, that doesn't work. It only returns records with something
in the field Notes, not those records that have a blank Notes field. I have
also tried <> "retired" and it does the same thing. I am thinking it has
something to do with a null value or "" empty text string in the blank
fields, but don't know how to solve it.

 

[John Spencer]

WHERE Notes not like "*Retired*" or Notes is Null

OR just as fast since you can't use the index anyway with that wild card search.

Force a value for notes by appending a zero length string to the notes field
(for the search)

WHERE Notes & "" Not Like ""*Retired*"

 

[Damon Heron]

Thank you Thank you Thank you!!!
Made my day. Now you know how boring my life is