Report system resource exceeded

Question

Hi everyone,

I am working on a budgeting database and am currently creating a report that compares the budget to the actual costs for one department.  There are a bunch of stacked queries to arrive at both the budget numbers and the actual numbers, and the results are also attached to the values of a form (setting the date range and which budget number to compare).  The budget and actual numbers are joined in a union between a left join of the two and a right join of the two (Many to many).

The query itself runs, slowly, which is what I'd expect at this point.  However, when I try to make a report of the query (and I made a fresh one with no totals or anything) I receive a "system resource exceeded" error.  I traced the queries the first time I did this and found one step that is the third from the bottom of the actual costs side of the query that I made into a table (the table will be able to be appended just once a month) and used this table in all of the subsequent queries instead of the original query.  This should have resolved some of the complexity.  However, the report is still showing the same "system resource exceeded" error. 

What could cause a report to be exceeded when a query isn't?

As a side note, the actual to budget comparison report for the company as a whole runs perfectly (though a bit slowly as well).

Thanks for your thoughts,

Claire

Answer 1

Hi,

Large and complex query criteria in the WHERE clause that gets a lot of memory. So you need to optimize your query. You can make table query, from the created table show the results in your report. or simply on open report event run you query using Docmd.openquery or Docmd.runquery, and assign recordsource of your report to your table name. For more about this, please refer to: http://bytes.com/topic/access/answers/561947-system-resource-exceeded-error

You can also check the following article related to this error.

http://support.microsoft.com/kb/918814

Regards