Dynamic SQL statement too large...

[Brad Pears]

I have an Access 2000 application where the user can select a multiude of
criteria to produce a report. When the user clicks the 'Print' button, I
then build a dynamic SQL statement based on the criteria selected.

I just had to add some additional criteria selection to the report at the
users request, but unfortunately it will now be possible for them to choose
so many different criteria, that my SQL statement can exceed the maximum
size as far as number of characters in the actual statement. At least I am
assuming that is what I am having the problem with - as that is what is
indicated in the error message I receive. I had read somewhere some time ago
that there is a max characters for an SQL statement.

Basically the critera is a bunch of list boxes filled with options. In each
of the listboixes, the user can select multiple items to report on. For some
of the listboxes, they may want to report on ALL of the items in there. So,
when I build my dynamic SQL statement, I am looping through all selected
items in each of the listboxes and building a where claused based on each
item selected.

This must obviously be a problem for other people as well. What is the best
way to overcome the problem? I was thinking about building a base SQL
statement, then build multiple where clauses (one for each of the list box
selections) - each one based on the previous query and in the end I would
only have the records I want - but what would be the best way to do that?

Can you think of any other way to do this at all???

Thanks, Brad

 

[Allen Browne]

From memory, a SQL statement you generate in code can be around 64k. That's
quite a bit of text.

If the code that handles your multi-select list boxes is generating:
(Table1.Field1 = "dog") OR (Table1.Field1 = "cat") or ...
you could use the IN operator:
Table1.Field1 IN ("dog", "cat", ...)

Another option is to add a combo above the list box where the user can
choose "Includes" or "Excludes". That way, if the user want 95 of the 100
items in the list, they can just select the 5 to exclude, and your target
statement is much smaller.

If your error message actually stated the SQL statement was "too complex"
rather than too long, there may be another factor here. It's a generic
message that just means Access can't parse the statement. It can happen with
mis-matched brackets, misunderstood data types, reserved words as
field/table names, and a bunch of other reasons.