It sounds like you want to create a table named tblExclusions consisting of
a single field 'Exclude' of type 'text'. Get all of the words you want to
exclude on in that table. Has the added benefit that once the SQL works, the
set of exclusions is editable without touching the query. Simply add or
delete records.
I was able to do something similar to what you want (I'll explain the
limitation in a bit) with two queries. The first builds a set of company
names that contain any of the excluded terms and looks like (I called this
qryExclude):
SELECT cn.CorpName FROM tblCorpNames AS cn, tblExclude AS x
WHERE cn.corpname Like "*" & x.Field1 & "*"
GROUP BY cn.corpname;
The second query then uses this query as follows:
SELECT DISTINCT cn.CorpName FROM tblCorpNames AS cn, qryExclude
WHERE cn.CorpName Not In (select * from qryExclude)
ORDER BY cn.CorpName;
The drawback is as follows. If tblExclude contains 'Corp' but not
'Corporation', then 'Corporation' will also be excluded. So if you wanted to
see 'Acme Corporation' but not 'Omega Corp', you wouldn't. The wild carding
doesn't let you ask for 'exactly this pattern', something that you would be
able to do with say Perl regular expressions using word boundaries. You
don't have them in SQL (something I've missed often).
Good Luck!
--
Chaim
James K via AccessMonster.com said:
I want to remove the records that have the word CO, Corp, Corporation,Inc,LTD,
PC, P.C.etc. The length of the NOT Like "*ABC*" and NOT LIKE "*XYZ*" exceeds
2048 the max for SQL in Access 2002. how do I shorten this down?
Duane's response raised the question in my mind: Do you mean to remove those
terms from the result? Or exclude any record having one of these terms in
the result?
The first question means: If the total field reads 'Acme Corp', just report
'Acme'. The second version means: If the total field reads 'Acme Corp',
exclude this row entirely from the result set.
--
Chaim
This will only work if the entire field value is CO, CORP, or Corporation. A
field with a value like "ABC Corporation" would be included in the
results.
[quoted text clipped - 12 lines]
When I do this, those items with CO, Corp, and Coporation are still in the
results. What have I missed.
