Not Like Operator

  • Thread starter Thread starter AFSSkier
  • Start date Start date
A

AFSSkier

I have 2 tables tempBrand_Work_Table & Brand_Work_Table. I trying to run a
query to find the BrandID's that are "NOT LIKE" in the orginal table.
BrandID is a text field. I even tryed <> with the same results.

I can't get the resolutes I looking for with the following SQL:

SELECT tempBrand_Work_Table.[AP-Master], tempBrand_Work_Table.ItemNo,
tempBrand_Work_Table.BrandID, tempBrand_Work_Table.BrandName,
Brand_Work_Table.BrandID, Brand_Work_Table.BrandName
FROM tempBrand_Work_Table LEFT JOIN Brand_Work_Table ON
tempBrand_Work_Table.ItemNo = Brand_Work_Table.ItemNo
WHERE (((Brand_Work_Table.BrandID) Not Like [tempBrand_Work_Table]![BrandID]))
ORDER BY tempBrand_Work_Table.[AP-Master];
 
The Like operator (and Not Like) use wildcards to allow you to
"approximate".

For example, if you used
Like Sm & *

you'd find
"Smith"
"Smythe"
"Smithfield"
"Smolenski"
but not
"Wellsmith"

If you want to use Not Like, try adding a wildcard...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Can you be a bit more specific on what you want?
Can you give an example where Brand_Work_Table.BrandID is Not Like
[tempBrand_Work_Table]![BrandID] when the ItemNo match?

Perhaps what you want is an unmatched query that is similar to the one below:

SELECT tempBrand_Work_Table.[AP-Master]
, tempBrand_Work_Table.ItemNo,
, tempBrand_Work_Table.BrandID
, tempBrand_Work_Table.BrandName
FROM tempBrand_Work_Table LEFT JOIN Brand_Work_Table
ON tempBrand_Work_Table.ItemNo = Brand_Work_Table.ItemNo
and [tempBrand_Work_Table]![BrandID] = Brand_Work_Table.BrandID
WHERE Brand_Work_Table.ItemNo is Null
ORDER BY tempBrand_Work_Table.[AP-Master];

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
I have 2 tables tempBrand_Work_Table & Brand_Work_Table. I trying to run a
query to find the BrandID's that are "NOT LIKE" in the orginal table.
BrandID is a text field. I even tryed <> with the same results.

If you want to find records in tempBrand_Work_Table which do not exist in
Brand_Work_Table, you should use the Unmatched Query Wizard. As Jeff and John
note, the LIKE operator has a very specific purpose - for matching partial
text strings. It is not intended for matching (presumably numeric) ID values!

The unmatched query wizard will generate something resembling this "frustrated
outer join" query:

SELECT tempBrand_Work_Table.[AP-Master], tempBrand_Work_Table.ItemNo,
tempBrand_Work_Table.BrandID, tempBrand_Work_Table.BrandName,
Brand_Work_Table.BrandID, Brand_Work_Table.BrandName
FROM tempBrand_Work_Table LEFT JOIN Brand_Work_Table ON
tempBrand_Work_Table.ItemNo = Brand_Work_Table.ItemNo
WHERE Brand_Work_Table.ItemNo IS NULL
ORDER BY tempBrand_Work_Table.[AP-Master];

The LEFT JOIN will return all records in tempBrand_Work_Table, and all
*matching* records in Brand_Work_Table; if there is no matching record it will
return all NULLS for the Brand_Work_Table fields. If you put the IS NULL
criterion on ItemNo, you'll limit retrieval to just those temp records which
have no match. The Brand_Work_Table.BrandName field will of necessity be null
(unknown) for these records, so I don't think there's any good reason to
include it in the query!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top