the expression entered exceeds the 1,024-character limit for the q

[Patrick Briggs]

Hi,

I'm using Access 2003 on a Windows XP box.

I've got a table of individuals donors and organization donors. I don't
have a donor type field to work off of. I do have the LastName column though
- it's has legitimate last names for the Individuals and then full
organization names for Organizations.

I'm trying to create a query that can pick out the organization donors
based on criteria in a WHERE clause. For example:

WHERE (((Donor_Lengths_tbl.Ln) Like "*trust*")) OR (((Donor_Lengths_tbl.Ln)
Like "*temple*")) OR (((Donor_Lengths_tbl.Ln) Like "*church*"))

etc. My WHERE clause above contains 150 or statements

I run into the error:

"the expression you entered exceeds the 1,024-character limit for the query
design grid"

I also find that the WHERE clause coincidentally doesn't seem to work - pick
up the records I'm looking for based on the WHERE clause.

I'm working in SQL view to create this query so I don't care about not being
able to see it visually in Query Design Grid, but I suspect this has
something to do with my query not working.

1) Is there an Access limitation that will cause the the query to give
unpredicable results if I exceed that limitation?

2) Is there a better way to do this? For example, I did create a table
with all the criteria (150 of 'em) - i.e. trust, church, temple, etc. Doing
this off a table would be great because it would get around this WHERE clause
limitation if it exists AND it would make for easier updating of the WHERE
clause criteria.

Hope somebody can shed some light.

Regards,

Patrick Briggs,
Pasadena, CA

 

[Ken Snell \(MVP\)]

Did you try entering the "long" WHERE clause in the SQL view of the query?
Should be ok there.

Perhaps the table idea would work if you were to use non-equi-joins, but
first I'd take a look at the entire query's structure to see if there is
another way to structure your data so that you can avoid using nonindexed
fields. Hard to say without knowing more about your data.

 

[Patrick Briggs]

Hi Ken,

Thanks for taking a look at my question. Again, my goal is to separate out
the Organization donors from the Individual donors.

I did try entering my query long form in the SQL view. It was actually
easier to edit there.

The table I'm querying off of (to get a view of most of the organizations)
looks like this (Fn - first name and Ln - last name:

DonorID Fn Ln TRIM(LEN(Ln))

text text text number

I have about 38,000 unique donor records. I was able to use a couple of
queries to knock out 36,000 records based on the last field's length being <=
10 (most last names fall into that category).

So I have about 2,400 records left to sift through with the query I'm trying
to create. I know after having done this the manual way, I should get it
down to about 1400 records which are Organization donors.

The current query is just 150 or so WHERE clause statements like this:

WHERE (((Donor_Lengths_tbl.Ln) Like "*trust*")) OR (((Donor_Lengths_tbl.Ln)
Like "*temple*")) OR (((Donor_Lengths_tbl.Ln) Like "*church*")) OR
(((Donor_Lengths_tbl.Ln) Like "*Inc.*")) OR (((Donor_Lengths_tbl.Ln) Like
"*Company*")) OR (((Donor_Lengths_tbl.Ln) Like "*Association*")) .......

I ran out of time today to isolate at which nth WHERE clause statement, the
query stopped working for me.

Does the above give you a better idea of what my table and query structures
are?

I had a thought that somehow I could put these WHERE clause statements into
a single column table:

Criteria

text

church
temple
inc.
company
trust
association....

and then use this table somehow in a simplified WHERE clause that is linked
to this field in the table but ...ran out of time today to see if I could
make this work. Is this even possible?

Hope this added information makes this easier to answer. If I find an
answer on my own tomorrow, you can be sure I'll post it here. I like being
able to contribute more than getting answers.

Regards,

Patrick Briggs

 

[John Spencer]

Personally, I would use the table and a non-equi join as Ken Snell suggested
as an alternative. And eventually, I would add a field to the Donor Table
to identify those donors that are organizations versus individuals.

Assumptions:
Donor Table (your current table)
LN - contains the organization name or the Last name of individual donors
OrgIdentifier Table (list of words to identify organizations)
OrgWord - Organization words such as Church, Company, etc

The query then would end up
SELECT *
FROM Donor INNER JOIN OrgIdentifier
ON Donor.LN LIKE "*" & OrgIdentifier.OrgWord "*"

A bit slower but perhaps more accurate would be to check for whole words
(that is, there must be a space before and after the OrgWord.

SELECT Donor.*
FROM Donor INNER JOIN OrgIdentifier
ON " " & Donor.LN & " " LIKE "* " & OrgIdentifier.OrgWord " *"

Of course, this technique can give you multiple returns if your organization
name was something like
"Association of Church Supply Manufacturers"

 

[Jerry Whittle]

There is a way to shorten up an SQL statement which might help. Use an alias
for the table names. Change the From clause to something like:

FROM Donor_Lengths_tbl as D

Then replace Donor_Lengths_tbl with D everywhere else in the SQL statement
like so to save 16 characters each time:
D.Ln

For example here is a full SQL statement.
SELECT Left([C].[Short_Description],InStr([C].[Short_Description],"(")-1) AS
ShortDesc
FROM Crystal as C
WHERE (((InStr([C].[Short_Description],"("))>0));

 

[John Vinson]

I'm trying to create a query that can pick out the organization donors
based on criteria in a WHERE clause. For example:

WHERE (((Donor_Lengths_tbl.Ln) Like "*trust*")) OR (((Donor_Lengths_tbl.Ln)
Like "*temple*")) OR (((Donor_Lengths_tbl.Ln) Like "*church*"))

I agree with Jerry - and in fact you can go a step further. If
Donor_Lengths_tbl is the only table in the query, or even if the
fieldname Ln occurs only in that table, you can leave out the
tablename altogether. You can also dispense with a lot of the
parentheses that the query grid throws in unnecessarily:

WHERE Ln LIKE "*trust*" OR Ln LIKE "*temple*" OR Ln LIKE "*church*"
OR...

I'd REALLY recommend normalizing your database and extracting the
category out of the name field. If you're seaching a mix of
organization and name fields, you *will* get false hits with last
names like Templeton, Churchill or the like.

John W. Vinson[MVP]

 

[Patrick Briggs]

John, Jerry and all,

1) An error in logic using

NOT LIKE "*trust*" OR NOT LIKE "*church*"

to ignore the Organizations and see just the individuals was why I kept
seeing organizations like "trust" or "church" in my query of the donor
records - duh. Should have been:

NOT LIKE "*trust*" AND NOT LIKE "*church*"

2) Yes normalizing the donor table such that a donor is either an I
(individual) or O (organization) would be ideal. That's how many of our
clients give us the results of a marketing campaign. Some don't though, and
for these I wanted a quick query that would get the lion's share of
Organization donors separated out.

3) You were right about the SQL view and suggesting aliasing my table to
avoid repeating the full table name throughout the WHERE clause

and

Getting rid of the extra parenthesis which query grid throws in
unnecessarily (why does Access do that I wonder).

4) You were also right that I could have my donor table and a org criteria
table non-equi-joined like so:

SELECT *
FROM Donor_Lengths_tbl as D
INNER JOIN Org_Criteria_tbl as O
ON D.Ln Like "* " & O.Org_Criteria & " *"

That's the first time I've done a join using LIKE . I like this solution
better than the lengthy WHERE clause that I had to do across 4 queries.

5) There is a limit to the number of WHERE clause statements at which point
Access will give me a message that the query is too complex (around 70-80
different OR statements). I broke up my lengthy WHERE clause into 4
different queries.

6) The query grid design view not working after 1,024 characters had
nothing to do with my problem in the end. This error is just a limitation of
Access' ability to display a complex query visually. The fall back to the
SQL design view works just fine.

Thanks to all of you for your help. I hope my response will be helpful to
anybody else that runs into a similar issue.

Regards,

Patrick Briggs

----------------------------------------------------