NOT LIKE search criteria

[Guest]

Hello all;

I have created a search query. I want to put in search criteria that would
filter the data. All I want to do is have the software look for two words in
the "comments" field, and if it finds one of these two words, then I don't
want that record displayed. I thought that if I used
Not Like "a*" OR Not Like "b*"
as criteria that the query would work, but it is returning all records WITH
"a" and "b" (the opposite of what I want). What is the proper way of
expressing my search criteria?

Thanks!
Casa

 

[David F Cox]

Like "a*" AND Like "b*"

 

[Guest]

hi,

Just change your NOT LIKE to:

Not Like "*b*" AND Not Like "*a*"

geebee

 

[Guest]

Thank you for your suggestion. I have tried it, and now I get no records in
my search results (there should be several thousand). It's almost as if the
software isn't noticing the "NOT" and going directly to the "Like" in my
search criteria.

Is there anything else I can try?

 

[Guest]

Thanks for your reply. Your suggestion produces no records in the search
result (I should get several thousand records). Basically what I want is a
search criteria that tells the software to look in my comments field for "a"
and "b". If these are found, then I DON'T want those records in my search
results. How do I express this in the search criteria?

 

[John Spencer]

Perhaps you need to describe your requirements a little better.

Do you want
-- Records that don't have both words
WHERE NOT(Comments Like "*Word1*" AND Comments Like "*Word2*")
WHERE Comments Not Like "*Word1*" AND Comments Not Like "*Word2*"
-- Records that have both words
WHERE Comments Like "*Word1*" AND Comments Like "*Word2*"
-- Records that have one of the words, but not both words
WHERE (Comments Not Like "*Word1*"
AND Comments Like "*Word2*") OR
(Comments Like "*Word1*"
AND Comments NOT Like "*Word2*")
-- Records that don't have either of the words
WHERE NOT(Comments Like "*Word1*" OR Comments Like "*Word2*")

 

[David F Cox]

sorry. :-<

...for "a" and "b". If these are found, then I DON'T want those records

NOT (Like "a*" AND Like "b*")

should remove records with both in the field.

NOT Like "a*" OR NOT Like "b*"

should remove records with either

 

[Guest]

I'm sorry about my ambiguousness. I want records that don't have either of
the two words in them. I tried your last code suggestion:

SELECT DDH_Sample_Intervals.Sample, DDH_Sample_Intervals.From,
DDH_Sample_Intervals.To, DDH_Sample_Intervals.Interval,
DDH_Sample_Intervals.Comment
FROM DDH_Sample_Intervals
WHERE NOT(Comment Like "*Standard*" OR Comment Like "*Blank*");

The query doesn't return any records. It should be returning several
thousand. Have I added the new code incorrectly?

 

[Guest]

hi,

Your WHERE clause should be...

WHERE [Comment] NOT Like "*Standard*" OR [Comment] NOT Like "*Blank*");

Before, you had...
WHERE NOT(Comment Like "*Standard*" OR Comment Like "*Blank*");

Just copy and paste this...

geebee

 

[John Spencer]

Although I don't think this is the problem since you are getting no records
returned, you can TRY using the ANSI 92 wild card character "%" instead of
Access Jet wild card character "*".

SELECT DDH_Sample_Intervals.Sample, DDH_Sample_Intervals.From,
DDH_Sample_Intervals.To, DDH_Sample_Intervals.Interval,
DDH_Sample_Intervals.Comment
FROM DDH_Sample_Intervals
WHERE NOT(Comment Like "%Standard%" OR Comment Like "%Blank%");

 

[Guest]

Thanks for the correction. This helps me out a lot! My code is now:

SELECT DDH_Sample_Intervals.Sample, DDH_Sample_Intervals.From,
DDH_Sample_Intervals.To, DDH_Sample_Intervals.Interval,
DDH_Sample_Intervals.Comment
FROM DDH_Sample_Intervals
WHERE [Comment] NOT Like "*Standard*" OR [Comment] NOT Like "*Blank*";

I had cut and paste the code from your post into my query, but the program
didn't like that last bracket, so I deleted it. Ok, so the query now returns
all the records that contain "Standard" or "Blank". I want the opposite
(everything EXCEPT the records that have "Standard" or "Blank" in the Comment
field). The code SEEMS fine. I don't understand what the problem is.

hi,

Your WHERE clause should be...

WHERE [Comment] NOT Like "*Standard*" OR [Comment] NOT Like "*Blank*");

Before, you had...
WHERE NOT(Comment Like "*Standard*" OR Comment Like "*Blank*");

Just copy and paste this...

geebee

Thanks for your reply. Your suggestion produces no records in the search
result (I should get several thousand records). Basically what I want is a
search criteria that tells the software to look in my comments field for "a"
and "b". If these are found, then I DON'T want those records in my search
results. How do I express this in the search criteria?

 

[Guest]

Ok, now that I've corrected my coding problem, when I use the * I'm returning
all the records that have "Standard" and "Blank" in the Comment field. I
want the opposite (i.e. everything EXCEPT the records that have "Standard" or
"Blank" in the Comment field). I tried your suggestion (using % instead of
*), and I receive the above result as well. Oh well, worth a try!

 

[Guest]

hi,

Change it to...

WHERE (((comment) Not Like "*standard*") AND ((comment) Not Like "*blank*"))

Cheers,
geebee

Thanks for the correction. This helps me out a lot! My code is now:

SELECT DDH_Sample_Intervals.Sample, DDH_Sample_Intervals.From,
DDH_Sample_Intervals.To, DDH_Sample_Intervals.Interval,
DDH_Sample_Intervals.Comment
FROM DDH_Sample_Intervals
WHERE [Comment] NOT Like "*Standard*" OR [Comment] NOT Like "*Blank*";

I had cut and paste the code from your post into my query, but the program
didn't like that last bracket, so I deleted it. Ok, so the query now returns
all the records that contain "Standard" or "Blank". I want the opposite
(everything EXCEPT the records that have "Standard" or "Blank" in the Comment
field). The code SEEMS fine. I don't understand what the problem is.

hi,

Your WHERE clause should be...

WHERE [Comment] NOT Like "*Standard*" OR [Comment] NOT Like "*Blank*");

Before, you had...
WHERE NOT(Comment Like "*Standard*" OR Comment Like "*Blank*");

Just copy and paste this...

geebee

Thanks for your reply. Your suggestion produces no records in the search
result (I should get several thousand records). Basically what I want is a
search criteria that tells the software to look in my comments field for "a"
and "b". If these are found, then I DON'T want those records in my search
results. How do I express this in the search criteria?

:

Like "a*" AND Like "b*"


Hello all;

I have created a search query. I want to put in search criteria that
would
filter the data. All I want to do is have the software look for two words
in
the "comments" field, and if it finds one of these two words, then I don't
want that record displayed. I thought that if I used
Not Like "a*" OR Not Like "b*"
as criteria that the query would work, but it is returning all records
WITH
"a" and "b" (the opposite of what I want). What is the proper way of
expressing my search criteria?

Thanks!
Casa

 

[David F Cox]

i missed out leading *
tested and working:

Not Like "*cats*" And Not Like "*dogs*"

hi,

Change it to...

WHERE (((comment) Not Like "*standard*") AND ((comment) Not Like
"*blank*"))

Cheers,
geebee

Thanks for the correction. This helps me out a lot! My code is now:

SELECT DDH_Sample_Intervals.Sample, DDH_Sample_Intervals.From,
DDH_Sample_Intervals.To, DDH_Sample_Intervals.Interval,
DDH_Sample_Intervals.Comment
FROM DDH_Sample_Intervals
WHERE [Comment] NOT Like "*Standard*" OR [Comment] NOT Like "*Blank*";

I had cut and paste the code from your post into my query, but the
program
didn't like that last bracket, so I deleted it. Ok, so the query now
returns
all the records that contain "Standard" or "Blank". I want the opposite
(everything EXCEPT the records that have "Standard" or "Blank" in the
Comment
field). The code SEEMS fine. I don't understand what the problem is.

hi,

Your WHERE clause should be...

WHERE [Comment] NOT Like "*Standard*" OR [Comment] NOT Like "*Blank*");

Before, you had...
WHERE NOT(Comment Like "*Standard*" OR Comment Like "*Blank*");

Just copy and paste this...

geebee



:

Thanks for your reply. Your suggestion produces no records in the
search
result (I should get several thousand records). Basically what I
want is a
search criteria that tells the software to look in my comments field
for "a"
and "b". If these are found, then I DON'T want those records in my
search
results. How do I express this in the search criteria?

:

Like "a*" AND Like "b*"


Hello all;

I have created a search query. I want to put in search criteria
that
would
filter the data. All I want to do is have the software look for
two words
in
the "comments" field, and if it finds one of these two words,
then I don't
want that record displayed. I thought that if I used
Not Like "a*" OR Not Like "b*"
as criteria that the query would work, but it is returning all
records
WITH
"a" and "b" (the opposite of what I want). What is the proper
way of
expressing my search criteria?

Thanks!
Casa