Problem with Query NOT IN statements

  • Thread starter Thread starter James K via AccessMonster.com
  • Start date Start date
J

James K via AccessMonster.com

I want to Query a Table using the Query builder. I want to find certain
words and exclude them from the results.

I was told by a friend to use the following syntax. NOT IN ("CO","CORP",
"Corporation")

When I do this, those items with CO, Corp, and Coporation are still in the
results. What have I missed.
 
James,

The syntax you have used is applicable if the values you are trying to
exclude are the entire entry in the field. Is that the case? And you
have put this in the Criteria of the field in question? Or do you mean
to exclude entries where CO, CORP, etc are included in the text in the
field? If that's the case, you may need something like this...
Not Like "*CO *" And Not Like "*CORP *"
 
It's the latter. But when I do that, I exceed the 2048 limit of SQL and 1024
of the Expression Builder. How do I get arouund this limit?


Steve said:
James,

The syntax you have used is applicable if the values you are trying to
exclude are the entire entry in the field. Is that the case? And you
have put this in the Criteria of the field in question? Or do you mean
to exclude entries where CO, CORP, etc are included in the text in the
field? If that's the case, you may need something like this...
Not Like "*CO *" And Not Like "*CORP *"
I want to Query a Table using the Query builder. I want to find certain
words and exclude them from the results.
Click to expand...
[quoted text clipped - 4 lines]
When I do this, those items with CO, Corp, and Coporation are still in the
results. What have I missed.
Click to expand...
Click to expand...
 
James,

Just a wild stab in the dark here. But I guess this can happen if...
- the name of the table(s) and/or fields are very long, and/or
- you have a large number of fields included in the query, and/or
- you have some calculated fields on the query, with long expressions,
and/or
- your want to exclude data according to a larger number of criteria
than you have let on so far.

Am I getting warm on any of these?
 
Back
Top