Query Too Complex

[Guest]

Dear All,

My organisation has recently upgraded from Access97 to Access2002 and many
of the reports I built in Access97 no longer work. The error message 'Query
too Complex' is always returned - even if the report is based on a table.

I wonder if microsoft have reduced to number of functions reports can handle
as these reports are quite complex, they have many levels and the controls
can contain more than one function.

When i reduce the number of controls on the report the report does run,
however this not an ideal solution. I wondered if anyone else had encountered
this problem and knew a work around. It seems that in terms of report
complexity microsft have taken a step backward from 97 to 2002.

I also have the same problem running a very simple report that is based on a
union query.

Any help would be greatly appriecated.

Many thanks,

Adam.

 

[Allen Browne]

The 'Query too complex' message means that Access is not able to interpret
how to run the query. (If the report is based directly on a table, that
could include the query that the report itself creates in order to perform
the Sorting And Grouping you requested.)

The functionality has not been reduced since A97, so the fact that the same
thing worked there indicates that the query is not too complex.

However, JET 4 (the query engine in Access 2000 and later) is certainly
poorer at understanding the data types of calculated query fields, and it
generates this error when it gets confused about how to apply criteria or if
the types don't match. It is highly likely that this is the cause or the
problem with the UNION query, and probably the others as well.

The solution is to explicitly typecast the calculated fields in the query.
For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The article should also encourage you to declare any parameters in your
queries. For example, if your OrderID field has this in the Criteria row:
[Forms].[Form1].[OrderID]
then choose Parameters on the Query menu (in query design), and in the
dialog enter:
[Forms].[Form1].[OrderID] Long

For the UNION query, the data type is determined from the first SELECT
statement, so if you have:
SELECT ID, Null As Expr1 FROM ...
then the 2nd field will be intrepreted as text, regardless of the data type
of the 2nd column in the 2nd SELECT. To avoid that, reverst the statements,
typecast the field, or use this fudge to get it interpreted as a number:
SELECT ID, IIf(False, 0, Null) As Expr1 FROM ...

 

[Guest]

Many thanks for this Allen, it is very useful.

The biggest problem I have is the report not running when it is based
directly on a table. The only solution I had to this was to remove some of
the grouping levels, however this is by no means ideal.

Do you know of anything I can do to make this report run with the number of
grouping levels I want?

Thanks again,

Adam.

The 'Query too complex' message means that Access is not able to interpret
how to run the query. (If the report is based directly on a table, that
could include the query that the report itself creates in order to perform
the Sorting And Grouping you requested.)

The functionality has not been reduced since A97, so the fact that the same
thing worked there indicates that the query is not too complex.

However, JET 4 (the query engine in Access 2000 and later) is certainly
poorer at understanding the data types of calculated query fields, and it
generates this error when it gets confused about how to apply criteria or if
the types don't match. It is highly likely that this is the cause or the
problem with the UNION query, and probably the others as well.

The solution is to explicitly typecast the calculated fields in the query.
For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The article should also encourage you to declare any parameters in your
queries. For example, if your OrderID field has this in the Criteria row:
[Forms].[Form1].[OrderID]
then choose Parameters on the Query menu (in query design), and in the
dialog enter:
[Forms].[Form1].[OrderID] Long

For the UNION query, the data type is determined from the first SELECT
statement, so if you have:
SELECT ID, Null As Expr1 FROM ...
then the 2nd field will be intrepreted as text, regardless of the data type
of the 2nd column in the 2nd SELECT. To avoid that, reverst the statements,
typecast the field, or use this fudge to get it interpreted as a number:
SELECT ID, IIf(False, 0, Null) As Expr1 FROM ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My organisation has recently upgraded from Access97 to Access2002 and many
of the reports I built in Access97 no longer work. The error message
'Query
too Complex' is always returned - even if the report is based on a table.

I wonder if microsoft have reduced to number of functions reports can
handle
as these reports are quite complex, they have many levels and the controls
can contain more than one function.

When i reduce the number of controls on the report the report does run,
however this not an ideal solution. I wondered if anyone else had
encountered
this problem and knew a work around. It seems that in terms of report
complexity microsft have taken a step backward from 97 to 2002.

I also have the same problem running a very simple report that is based on
a
union query.

Any help would be greatly appriecated.

Many thanks,

Adam.

 

[Allen Browne]

How many grouping levels do you need?
From memory, the limit is about 9.

Is it failing with fewer than that?
If so, what data types are in use at each level?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Many thanks for this Allen, it is very useful.

The biggest problem I have is the report not running when it is based
directly on a table. The only solution I had to this was to remove some of
the grouping levels, however this is by no means ideal.

Do you know of anything I can do to make this report run with the number
of
grouping levels I want?

Thanks again,

Adam.

The 'Query too complex' message means that Access is not able to
interpret
how to run the query. (If the report is based directly on a table, that
could include the query that the report itself creates in order to
perform
the Sorting And Grouping you requested.)

The functionality has not been reduced since A97, so the fact that the
same
thing worked there indicates that the query is not too complex.

However, JET 4 (the query engine in Access 2000 and later) is certainly
poorer at understanding the data types of calculated query fields, and it
generates this error when it gets confused about how to apply criteria or
if
the types don't match. It is highly likely that this is the cause or the
problem with the UNION query, and probably the others as well.

The solution is to explicitly typecast the calculated fields in the
query.
For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The article should also encourage you to declare any parameters in your
queries. For example, if your OrderID field has this in the Criteria row:
[Forms].[Form1].[OrderID]
then choose Parameters on the Query menu (in query design), and in the
dialog enter:
[Forms].[Form1].[OrderID] Long

For the UNION query, the data type is determined from the first SELECT
statement, so if you have:
SELECT ID, Null As Expr1 FROM ...
then the 2nd field will be intrepreted as text, regardless of the data
type
of the 2nd column in the 2nd SELECT. To avoid that, reverst the
statements,
typecast the field, or use this fudge to get it interpreted as a number:
SELECT ID, IIf(False, 0, Null) As Expr1 FROM ...

My organisation has recently upgraded from Access97 to Access2002 and
many
of the reports I built in Access97 no longer work. The error message
'Query
too Complex' is always returned - even if the report is based on a
table.

I wonder if microsoft have reduced to number of functions reports can
handle
as these reports are quite complex, they have many levels and the
controls
can contain more than one function.

When i reduce the number of controls on the report the report does run,
however this not an ideal solution. I wondered if anyone else had
encountered
this problem and knew a work around. It seems that in terms of report
complexity microsft have taken a step backward from 97 to 2002.

I also have the same problem running a very simple report that is based
on
a
union query.

Any help would be greatly appriecated.

Many thanks,

Adam.

 

[Guest]

The limit is 9. I am only using 6 and the query is failing. When I reduce the
number to 3 it works.

The grouping fields are set at run-time depending upon criteria the user has
selected but they are always text fields.

Each grouping level has a header and footer which contain numerous controls
which contain iif() statements and sum()'s.

Its really irritating that this worked fine in Acc97.

Thanks,

Adam.

How many grouping levels do you need?
From memory, the limit is about 9.

Is it failing with fewer than that?
If so, what data types are in use at each level?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Many thanks for this Allen, it is very useful.

The biggest problem I have is the report not running when it is based
directly on a table. The only solution I had to this was to remove some of
the grouping levels, however this is by no means ideal.

Do you know of anything I can do to make this report run with the number
of
grouping levels I want?

Thanks again,

Adam.

The 'Query too complex' message means that Access is not able to
interpret
how to run the query. (If the report is based directly on a table, that
could include the query that the report itself creates in order to
perform
the Sorting And Grouping you requested.)

The functionality has not been reduced since A97, so the fact that the
same
thing worked there indicates that the query is not too complex.

However, JET 4 (the query engine in Access 2000 and later) is certainly
poorer at understanding the data types of calculated query fields, and it
generates this error when it gets confused about how to apply criteria or
if
the types don't match. It is highly likely that this is the cause or the
problem with the UNION query, and probably the others as well.

The solution is to explicitly typecast the calculated fields in the
query.
For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The article should also encourage you to declare any parameters in your
queries. For example, if your OrderID field has this in the Criteria row:
[Forms].[Form1].[OrderID]
then choose Parameters on the Query menu (in query design), and in the
dialog enter:
[Forms].[Form1].[OrderID] Long

For the UNION query, the data type is determined from the first SELECT
statement, so if you have:
SELECT ID, Null As Expr1 FROM ...
then the 2nd field will be intrepreted as text, regardless of the data
type
of the 2nd column in the 2nd SELECT. To avoid that, reverst the
statements,
typecast the field, or use this fudge to get it interpreted as a number:
SELECT ID, IIf(False, 0, Null) As Expr1 FROM ...


My organisation has recently upgraded from Access97 to Access2002 and
many
of the reports I built in Access97 no longer work. The error message
'Query
too Complex' is always returned - even if the report is based on a
table.

I wonder if microsoft have reduced to number of functions reports can
handle
as these reports are quite complex, they have many levels and the
controls
can contain more than one function.

When i reduce the number of controls on the report the report does run,
however this not an ideal solution. I wondered if anyone else had
encountered
this problem and knew a work around. It seems that in terms of report
complexity microsft have taken a step backward from 97 to 2002.

I also have the same problem running a very simple report that is based
on
a
union query.

Any help would be greatly appriecated.

Many thanks,

Adam.

 

[Allen Browne]

These IIf() statements that are being summed in the group footers... any
chance of moving them into a query that feeds the report? That is turn them
into calculated query fields, and explicitly typecast the results so Access
can't get them wrong?

That should at least take the burden out of the grouping levels, so as to
make that easier for the report, and it may also help you identify the part
Access is getting confused over.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The limit is 9. I am only using 6 and the query is failing. When I reduce
the
number to 3 it works.

The grouping fields are set at run-time depending upon criteria the user
has
selected but they are always text fields.

Each grouping level has a header and footer which contain numerous
controls
which contain iif() statements and sum()'s.

Its really irritating that this worked fine in Acc97.

Thanks,

Adam.

How many grouping levels do you need?
From memory, the limit is about 9.

Is it failing with fewer than that?
If so, what data types are in use at each level?

Many thanks for this Allen, it is very useful.

The biggest problem I have is the report not running when it is based
directly on a table. The only solution I had to this was to remove some
of
the grouping levels, however this is by no means ideal.

Do you know of anything I can do to make this report run with the
number
of
grouping levels I want?

Thanks again,

Adam.




:

The 'Query too complex' message means that Access is not able to
interpret
how to run the query. (If the report is based directly on a table,
that
could include the query that the report itself creates in order to
perform
the Sorting And Grouping you requested.)

The functionality has not been reduced since A97, so the fact that the
same
thing worked there indicates that the query is not too complex.

However, JET 4 (the query engine in Access 2000 and later) is
certainly
poorer at understanding the data types of calculated query fields, and
it
generates this error when it gets confused about how to apply criteria
or
if
the types don't match. It is highly likely that this is the cause or
the
problem with the UNION query, and probably the others as well.

The solution is to explicitly typecast the calculated fields in the
query.
For details, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

The article should also encourage you to declare any parameters in
your
queries. For example, if your OrderID field has this in the Criteria
row:
[Forms].[Form1].[OrderID]
then choose Parameters on the Query menu (in query design), and in the
dialog enter:
[Forms].[Form1].[OrderID] Long

For the UNION query, the data type is determined from the first SELECT
statement, so if you have:
SELECT ID, Null As Expr1 FROM ...
then the 2nd field will be intrepreted as text, regardless of the data
type
of the 2nd column in the 2nd SELECT. To avoid that, reverst the
statements,
typecast the field, or use this fudge to get it interpreted as a
number:
SELECT ID, IIf(False, 0, Null) As Expr1 FROM ...


My organisation has recently upgraded from Access97 to Access2002
and
many
of the reports I built in Access97 no longer work. The error message
'Query
too Complex' is always returned - even if the report is based on a
table.

I wonder if microsoft have reduced to number of functions reports
can
handle
as these reports are quite complex, they have many levels and the
controls
can contain more than one function.

When i reduce the number of controls on the report the report does
run,
however this not an ideal solution. I wondered if anyone else had
encountered
this problem and knew a work around. It seems that in terms of
report
complexity microsft have taken a step backward from 97 to 2002.

I also have the same problem running a very simple report that is
based
on
a
union query.

Any help would be greatly appriecated.

Many thanks,

Adam.