--0-1050940505-1016741369=:94381
Content-Type: text/plain; charset=us-ascii


I seem to be up against an (unpublished?) ACCESS limitation. I get a "query too complex" error when running a report who's control
source is set to a query that runs without errors. The underlying query is very close to the 99 limitation on "AND"s in the SQL
statement. But I've taken everything out of the query which I can live without. The report does have some SUMs and IIF statements in
text boxes -- are those adding cumulatively and hitting some internal limitation?

HELP!

Also, can anybody explain to me the difference between "query too complex" and "system resources exceeded"? I get either message
when I'm just past the edge of the 99 SQL joins limitation... My only way around this has been to use Make Table Queries to write to
tables. Problem is: then I have to update the tables by attaching macros to the report push-buttons so users get the latest updates
on their reports.

George Talus

www.birkieskier2003@y...



---------------------------------
Do You Yahoo!?
Yahoo! Movies - coverage of the 74th Academy Awards®

Hi George

One way round this limit is to split your query into two or more queries 
and then join them in a 'master' query that you can then use to produce 
your report. This allows Access to work out the result set of each query 
seperatly, store the results in temporary tables, preventing it from 
getting 'lost'

Brian

> --0-1050940505-1016741369=:94381
Content-Type: text/plain; charset=us-ascii


I seem to be up against an (unpublished?) ACCESS limitation. I get 
a "query too complex" error when running a report who's control source is 
set to a query that runs without errors. The underlying query is very 
close to the 99 limitation on "AND"s in the SQL statement. But I've taken 
everything out of the query which I can live without. The report does have 
some SUMs and IIF statements in text boxes -- are those adding 
cumulatively and hitting some internal limitation?

HELP!

Also, can anybody explain to me the difference between "query too complex" 
and "system resources exceeded"? I get either message when I'm just past 
the edge of the 99 SQL joins limitation... My only way around this has 
been to use Make Table Queries to write to tables. Problem is: then I have 
to update the tables by attaching macros to the report push-buttons so 
users get the latest updates on their reports.

George Talus

www.birkieskier2003@y...



---------------------------------
Do You Yahoo!?
Yahoo! Movies - coverage of the 74th Academy Awards®

Are there any 'Error#' text appearing in the underlying query? You may be 
trying to perform a calculation that causes an error in the query but 
still allows it to run, e.g. you may be dividing by a number field but one 
of the records has a zero in that field causing an error. Or you may be 
totalling fields where one field is empty etc etc. If you do find any 
errors in the query (look carefully) you could copy the database, delete 
the offending records and try the report again. If it does work you may 
need to add some extra rules (input masks, validation rules etc) to your 
tables to prevent users from entering data that will cause this problem.

Paul