Report Formatting Hangs Up Forever

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a query and used it as the control source for a report. I tested
it in my test database, which has limited data, and it worked great. Then I
tested it in a copy of the "real" database. When I ran it there, a progress
bar appeared on the status area with the message, "Formatting report - press
ctrl + Break to stop..." But the report never formats. I waited for about
20 minutes once, and it never completed.

It occurred to me that the query was taking a long time, so I tried running
the query by itself, and it completed within a couple seconds, no problem.

Any ideas what could be hanging up the report formatting?
 
This could be a range of things.

From what I can gather, Access seems to build further queries into your
source query, based on what you put into the Sorting And Grouping box. In
order for these upper-level internal queries to run, it could require your
original query to run to completion, and that's very different than seeing
the first screenful of that query.

It is hard to know how to optimise the data without specifics. For example,
a crosstab might be optimized merely by setting the Column Headings
property. Indexing the fields used in criteria and sorting is an obvious
start. Using the correct data types makes a difference, e.g. declaring
parameters and typecasting calculated fields. If you have criteria under a
field in a Totals query, it might help to drag the fields into the query
grid a 2nd time, choose Where instead of Group By in the total row, and move
the criteria here (so it ends up in the WHERE clause rather than the HAVING
clause.)

There are *many* other possible bottlenecks as well that can cause a report
to format very slowly. Any code in the events of the report's sections,
anything that dynamically hides, shows, formats, resizes, or repeats
sections, anything forces page breaks or sets the report's runtime
properties (PrintSection, MoveLayout, NextRecord), any function calls in the
Control Source (esp. if it performs an OpenRecorset() or DLookup()), and so
on.

HTH

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

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

message
news:[email protected]...
 
Try looking at the properties of the report sections. If you have keep
together checked, uncheck it and see what happens. Do the same thing in the
Grouping and Sorting dialog.

I had this problem with one report where a memo field was larger than one
page. Access seemed to be hunting for a page large enough to hold the
entire memo field. Of course, it couldn't find one.

I discovered the problem because I could print up to a certain record (using
criteria to limit the results returned) and I could print after that record.
Examining the record in question, showed me that the memo field was filling
two pages. When I temporarily truncated the memo field, all was well.

This may or may not be your problem, but it is one thing to check.
 
John, that was the problem exactly. I unchecked Keep Together, and it worked
great. Just as in your experience, a couple of the memo fields were too long
to fit on a single page.

Thanks so much for saving me a lot of time.
 
Allen, thanks again for your time. It turned out to be something fairly
simple, but just outside my limited experience so far--see my reply to John
Spencer. However I appreciate the information you gave me, and will keep
those things in mind if and when I deal with more sophisticated report
manipulations.
 
Back
Top