Too Complex Error

[Kyra2009]

My database uses a form to set the perameters for the reports. I am having
the same issue with 2 reports:

1) ALL RESULTS is run by date, chosen by datepickers. The report is fine
for running all of January only, February only, and all of both January and
February. Trying to run March only or January thru March (or even January
thru December) results in the "Too Complex" error. There is data for March
on the table the report pulls from.

2) SINGLE PERSON RESULTS is run by date (datepickers again) for a single
person, chosen from a droplist on the form. This report runs fine for all
names for January only. Names 2 and 3 result in the "Too Complex" error when
trying to run any other single month and also when running any combination of
months. Name1, however, does just fine under all date ranges.

Additional Note: The queries for both reports run fine under all date ranges
and name selections.

If anyone could please help me understand the problem, I'd be greatly
appreciative.

Thanks!

 

[KARL DEWEY]

Post the SQL of your queries.

 

[Kyra2009]

Report 1:

SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4,
Query2.Field1, Query2.Field2
FROM [Query1] LEFT JOIN Query2 ON ([Query1].Field1 = Query2.Field1) AND
([Query1].Field2 = Query2.Field2)
WHERE ((([Query1].Field3) Between [Forms]![frmReportMenu]![StartDate] And
[Forms]![frmReportMenu]![EndDate]));


Report 2:

SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4,
Query2.Field1, Query2.Field2
FROM [Query1] LEFT JOIN Query2 ON ([Query1].Field1 = Query2.Field1) AND
([Query1].Field2 = Query2.Field2)
WHERE ((([Query1].Field1)=[Forms]![frmReportMenu]![Data]) AND
(([Query1].Field3) Between [Forms]![frmReportMenu]![StartDate] And
[Forms]![frmReportMenu]![EndDate]));



Some things I've tried:

No null values -- in one of these threads someone discovered that one of the
record fields was null and was causing problems in producing the report.
This did not seem to be the issue since there are null values in all months
of data. So if null caused a problem for March, it should have caused a
problem for January, but it didn't. But I tried anyway and filled in all
null values (some are supposed to be, but I put data in just to check), no
luck.

Temporary Tables -- another thread mentioned making the query a make table
query to create a temporary table and then report directly off the table
instead of off the query. I get the same results (ie January ok, March not
ok).

Sandbox -- the internet and Microsoft's website talk about a sandbox. I
read the fix on this and it is simply not possible to do this in the
environment the database is used. I am unable to test this to see if it is
the cause, but if it were I would think the reports would not work at all if
this were the case.

Rebuilding the Reports -- For Report 1, I found that one field was causing
the problem. If I do not include that field in the report, it runs fine.
Once I add that field to the report, it errors. For Report 2, that same
field has no effect and it runs fine for all names (although still only for
January). I have not yet completely rebuilt Report 2 to see if there is
another field causing the problem there instead.


I'm just having a really hard time finding any consistency in this to be
able to troubleshoot it. I'm not understanding why it is picking and
choosing which variation of the report to process and which to error. Any
ideas are welcome.

Thanks!

 

[KARL DEWEY]

I expected to to post Query1 and Query2, not the record source of the
reports.
If you run the SQL you posted what is the results?

I am not familiar with 'datepickers' and Access Help does not recognize the
term.

How do the names get entered in as criteria?
You did not say which field it was.

Report 1:

SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4,
Query2.Field1, Query2.Field2
FROM [Query1] LEFT JOIN Query2 ON ([Query1].Field1 = Query2.Field1) AND
([Query1].Field2 = Query2.Field2)
WHERE ((([Query1].Field3) Between [Forms]![frmReportMenu]![StartDate] And
[Forms]![frmReportMenu]![EndDate]));


Report 2:

SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4,
Query2.Field1, Query2.Field2
FROM [Query1] LEFT JOIN Query2 ON ([Query1].Field1 = Query2.Field1) AND
([Query1].Field2 = Query2.Field2)
WHERE ((([Query1].Field1)=[Forms]![frmReportMenu]![Data]) AND
(([Query1].Field3) Between [Forms]![frmReportMenu]![StartDate] And
[Forms]![frmReportMenu]![EndDate]));



Some things I've tried:

No null values -- in one of these threads someone discovered that one of the
record fields was null and was causing problems in producing the report.
This did not seem to be the issue since there are null values in all months
of data. So if null caused a problem for March, it should have caused a
problem for January, but it didn't. But I tried anyway and filled in all
null values (some are supposed to be, but I put data in just to check), no
luck.

Temporary Tables -- another thread mentioned making the query a make table
query to create a temporary table and then report directly off the table
instead of off the query. I get the same results (ie January ok, March not
ok).

Sandbox -- the internet and Microsoft's website talk about a sandbox. I
read the fix on this and it is simply not possible to do this in the
environment the database is used. I am unable to test this to see if it is
the cause, but if it were I would think the reports would not work at all if
this were the case.

Rebuilding the Reports -- For Report 1, I found that one field was causing
the problem. If I do not include that field in the report, it runs fine.
Once I add that field to the report, it errors. For Report 2, that same
field has no effect and it runs fine for all names (although still only for
January). I have not yet completely rebuilt Report 2 to see if there is
another field causing the problem there instead.


I'm just having a really hard time finding any consistency in this to be
able to troubleshoot it. I'm not understanding why it is picking and
choosing which variation of the report to process and which to error. Any
ideas are welcome.

Thanks!

 

[Kyra2009]

I expected to to post Query1 and Query2, not the record source of the

reports.

Sorry, I guess I misunderstood what you meant by posting the SQL of the
queries. This is the SQL of the queries used to populate the reports. But
I'm still a little confused on what you want to see.

If you run the SQL you posted what is the results?

The queries run fine, it's bringing the information into the reports that
causes the problem.

I am not familiar with 'datepickers' and Access Help does not recognize the
term.

I don't know what they're officially called, but it's the ActiveX control
with the calendar icon. Click on the icon to open a calendar and choose the
date from that.

How do the names get entered in as criteria?

From a dropbox selection, list only.

You did not say which field it was.

It is a calculated field =Sum([Field1])
There are several other calculated fields in the report, using similar data
types that all work fine. For example, if i'm showing a total of how many
apples and how many oranges were sold this month (2 separate columns),
=Sum([Oranges]) works fine, =Sum([Apples]) causes the complex error.

Report 1:

SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4,
Query2.Field1, Query2.Field2
FROM [Query1] LEFT JOIN Query2 ON ([Query1].Field1 = Query2.Field1) AND
([Query1].Field2 = Query2.Field2)
WHERE ((([Query1].Field3) Between [Forms]![frmReportMenu]![StartDate] And
[Forms]![frmReportMenu]![EndDate]));


Report 2:

SELECT [Query1].Field1, [Query1].Field2, [Query1].Field3, [Query1].Field4,
Query2.Field1, Query2.Field2
FROM [Query1] LEFT JOIN Query2 ON ([Query1].Field1 = Query2.Field1) AND
([Query1].Field2 = Query2.Field2)
WHERE ((([Query1].Field1)=[Forms]![frmReportMenu]![Data]) AND
(([Query1].Field3) Between [Forms]![frmReportMenu]![StartDate] And
[Forms]![frmReportMenu]![EndDate]));



Some things I've tried:

No null values -- in one of these threads someone discovered that one of the
record fields was null and was causing problems in producing the report.
This did not seem to be the issue since there are null values in all months
of data. So if null caused a problem for March, it should have caused a
problem for January, but it didn't. But I tried anyway and filled in all
null values (some are supposed to be, but I put data in just to check), no
luck.

Temporary Tables -- another thread mentioned making the query a make table
query to create a temporary table and then report directly off the table
instead of off the query. I get the same results (ie January ok, March not
ok).

Sandbox -- the internet and Microsoft's website talk about a sandbox. I
read the fix on this and it is simply not possible to do this in the
environment the database is used. I am unable to test this to see if it is
the cause, but if it were I would think the reports would not work at all if
this were the case.

Rebuilding the Reports -- For Report 1, I found that one field was causing
the problem. If I do not include that field in the report, it runs fine.
Once I add that field to the report, it errors. For Report 2, that same
field has no effect and it runs fine for all names (although still only for
January). I have not yet completely rebuilt Report 2 to see if there is
another field causing the problem there instead.


I'm just having a really hard time finding any consistency in this to be
able to troubleshoot it. I'm not understanding why it is picking and
choosing which variation of the report to process and which to error. Any
ideas are welcome.

Thanks!

 

[Kyra2009]

I finally got it. It was a combination of several things going on. I was
able to fix it by hard-coding to convert values to numbers (they were being
read as text even though input as numbers), and doing a combination of the
"make temp table" and filling in nulls created by the queries (the original
tables were full, but the queries resulted in some nulls, so I filled them in
on the temp table) and then drew the report from the report from the table.