Error: Query to complex

[Martin]

Hi everyone,

I have a report with some fields, these fields have been calculated in a
query. When i try to run a sum on these fields in any groupfooter of the
report, I get the error: "Query to complex".

Does anyone know the reason why i get this error?

TIA,

Martin.

 

[Allen Browne]

This messsage indicates that Access is not able to understand the query.
There are several possible causes.

One obvious cause is that the query is, in fact, too complex. However, there
are other reasons why Access might not be able to make sense of the query. A
common one is that it fails to understand the intended data type of a
calculated field, and so it cannot apply the critiera. The solution is to
typecast the calculation, e.g.:
CVDate([InvoiceDate] + 30)
CCur(Nz([CreditAmount] - [DebitAmount],0))
More in this article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Reports actually generate a further query into whatever table/query you have
in the RecordSource, in order to sort and perform the grouping in the
report. So the query that is too complex can be that generated by the report
rather than anything you created.

 

[Guest]

I've also gotten this error in a database orginally written in Access 97 and
later converted to Access 2000. The error only occurs when the user is
running the db in Access 2002, and only when opening a particular report.
I've made the changes suggested (using csng to make sure the db knows the
data type in the query) but the error still occurs. Interestingly, other
reports based on the same query run fine. There are no calculated fields on
the report, but there are 6 grouping levels. The groups are based on fields,
not calculations.

I read somewhere that this was an issue with Jet 4.0 but my understanding is
that both 2000 and 2002 run on Jet 4.0..... could it be an issue with a
specific version of Jet 4.0?

This messsage indicates that Access is not able to understand the query.
There are several possible causes.

One obvious cause is that the query is, in fact, too complex. However, there
are other reasons why Access might not be able to make sense of the query. A
common one is that it fails to understand the intended data type of a
calculated field, and so it cannot apply the critiera. The solution is to
typecast the calculation, e.g.:
CVDate([InvoiceDate] + 30)
CCur(Nz([CreditAmount] - [DebitAmount],0))
More in this article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Reports actually generate a further query into whatever table/query you have
in the RecordSource, in order to sort and perform the grouping in the
report. So the query that is too complex can be that generated by the report
rather than anything you created.

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

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

I have a report with some fields, these fields have been calculated in a
query. When i try to run a sum on these fields in any groupfooter of the
report, I get the error: "Query to complex".

Does anyone know the reason why i get this error?

 

[Allen Browne]

JET 4 in general is less able to guess the correct data types than previous
versions of JET.

It may be that MS has addressed some of those issues in the 8 service packs
that have been released for JET 4. To see if there is a version difference
between your A2000 and A2002 installations, use the Windows Explorer to
locate the file msjet40.dll (typically in windows\system32), right-click and
choose Properties, and look on the Version tab. The minor version is a
4-digit number that should start with an 8 if you have Service Pack 8
installed, e.g. 4.0.8618.0

If you need the update, download from:
http://support.microsoft.com/gp/sp

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

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

I've also gotten this error in a database orginally written in Access 97
and
later converted to Access 2000. The error only occurs when the user is
running the db in Access 2002, and only when opening a particular report.
I've made the changes suggested (using csng to make sure the db knows the
data type in the query) but the error still occurs. Interestingly, other
reports based on the same query run fine. There are no calculated fields
on
the report, but there are 6 grouping levels. The groups are based on
fields,
not calculations.

I read somewhere that this was an issue with Jet 4.0 but my understanding
is
that both 2000 and 2002 run on Jet 4.0..... could it be an issue with a
specific version of Jet 4.0?

This messsage indicates that Access is not able to understand the query.
There are several possible causes.

One obvious cause is that the query is, in fact, too complex. However,
there
are other reasons why Access might not be able to make sense of the
query. A
common one is that it fails to understand the intended data type of a
calculated field, and so it cannot apply the critiera. The solution is to
typecast the calculation, e.g.:
CVDate([InvoiceDate] + 30)
CCur(Nz([CreditAmount] - [DebitAmount],0))
More in this article:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Reports actually generate a further query into whatever table/query you
have
in the RecordSource, in order to sort and perform the grouping in the
report. So the query that is too complex can be that generated by the
report
rather than anything you created.

I have a report with some fields, these fields have been calculated in
a
query. When i try to run a sum on these fields in any groupfooter of
the
report, I get the error: "Query to complex".

Does anyone know the reason why i get this error?