asked on

Multiple Not Like statements in query

Experts,

I have a data set of about 600,000 records that I've imported into Access. My requirement is to exclude records that have any words in a description field that exist in a list of 20-40 exclusion keywords. I'm thinking of managing the list of exsclusion keywords on a form connected to a table... tblKeywords. How would I write a query that would read the table values and create a query criteria such as
Select * FROM tblData
WHERE tblData.description not like 'A', or not like 'B' or not like 'C' etc.

Would I need to use VBA to rip the values from the keyword table?
Is there a limit to how many Not Like expressions i can put in a statement?
Not sure how to do this...

For the sake of clarity, if my keywords are Flowers, Funeral, Party, Birthday, Cake, etc, then i want to exclude any records that have any of these words in the description field.
So
"Retirement Party for Joe" - exclude
"Flowers for hospital visit" - exclude
"Legitimate Expense" - include

The simplest solution would to have a very long WHERE NOT LIKE expression. But, we'd like to be able to have a non technical person manage the list so hence, managing it on a form
thanks

ASKER CERTIFIED SOLUTION

n2fc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

If the keyword always will be the first word, you can filter these out like this:

Select *
From YourTable
Where RTrim(Left([Description], Instr([Description], " "))) Not In (Select KeyWord From tblKeywords)

Open in new window

With 600,000 records you may wish to add some other filtering as well.