Like, Not Like

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I hope someone can shed some light on this problem I am having.

I am quering a database that has 5794 records, and when I use the LIKE
syntax I will get back X number of records, when I use the NOT LIKE syntax
with the same LIKE value, the return is not what I expected (i.e.: 5794 - x
= NOT LIKE return)

For example, if I was to query the database on the Type field:
LIKE "*ADRG*" returns 140 records
NOT LIKE "*ADRG*" returns 4560 records
I would expect that these two totals should equal the total number of
records in the database (5794) but it doesn't (it adds up to 4700).

I tried using DISTINCT as well with similar returns as well.

Also, I tried to search the Discussion board but was not able to find any
similar responses.

Any help would be greatly appreciated.
 
I hope someone can shed some light on this problem I am having.

I am quering a database that has 5794 records, and when I use the LIKE
syntax I will get back X number of records, when I use the NOT LIKE syntax
with the same LIKE value, the return is not what I expected (i.e.: 5794 - x
= NOT LIKE return)

For example, if I was to query the database on the Type field:
LIKE "*ADRG*" returns 140 records
NOT LIKE "*ADRG*" returns 4560 records
I would expect that these two totals should equal the total number of
records in the database (5794) but it doesn't (it adds up to 4700).
Click to expand...

My guess would be that the "missing" records have NULL in the Type
field. A NULL value is not "like" anything - nor is it "not like"
anything! Neither criterion you cite will retrieve the record.

If you want to show records with a NULL value in Type, use

NOT LIKE "*" & [Enter types you don't want to see:] & "*" OR IS NULL

as a criterion.


John W. Vinson[MVP]
 
John,

Thank you very much, the IS NULL returned the missing records. I appreciate
the quick reply.

Rod

John Vinson said:
I hope someone can shed some light on this problem I am having.

I am quering a database that has 5794 records, and when I use the LIKE
syntax I will get back X number of records, when I use the NOT LIKE syntax
with the same LIKE value, the return is not what I expected (i.e.: 5794 - x
= NOT LIKE return)

For example, if I was to query the database on the Type field:
LIKE "*ADRG*" returns 140 records
NOT LIKE "*ADRG*" returns 4560 records
I would expect that these two totals should equal the total number of
records in the database (5794) but it doesn't (it adds up to 4700).
Click to expand...

My guess would be that the "missing" records have NULL in the Type
field. A NULL value is not "like" anything - nor is it "not like"
anything! Neither criterion you cite will retrieve the record.

If you want to show records with a NULL value in Type, use

NOT LIKE "*" & [Enter types you don't want to see:] & "*" OR IS NULL

as a criterion.


John W. Vinson[MVP]
Click to expand...
 
Back
Top