Query works but report fails

[Scott Cragun]

I have a report based off a query that has started to fail. I have a copy of
my database from 4-27 where it works and another copy from 4-28 where I've
added a lot of functionality for something else completely unrelated to the
report that fails. In the newer copy it works a few times but after a while
it begins to fail. The query the report is based off of always works and
generally takes 5-10 seconds to run. When I run the report, it doesn't pop
up (even after many minutes) and when you look at the task manager it is
using all of the computer's resources. I've let this run for 30 minutes and
the report never pops up and the entire time it's consuming all resources. I
have to ctrl-alt-del and end the process to get out of it. When I go back
in, the query still takes just a few seconds but the report still won't open
(and it never displays an actual error message). Any ideas?

 

[Allen Browne]

It's possible the report is corrupt, but a more likely answer is that the
report is unable to plot a layout solution.

To test this idea, open the report in design view. Open the Properties box,
and for each section in the report, set the Keep Together property to No.
(On the Format tab of the Properties box, when looking at the properties of
a section.)

Then open the Sorting And Grouping pane. Select each group header in turn,
and set them to:
Do not keep group together on one page.

If this solves the problem, you have verified that the report is constantly
advancing through the records to test if all these records will fit on the
page, and then retreating back through them when they don't, and repeating
the same thing on the next page and so on, without finding a workable
solution.

While you are there, what fields are you sorting or grouping on? Are these
fields indexed in their respective tables?

 

[Petr Danes]

I had a report go off the deep end that way once, turned out I had a 'Keep
together' property turned on erroneously. The SECTION being generated didn't
fit on a page, so Access went to the next page, where it didn't fit either,
so it went to the next page...

Seems like the report engine ought to be smart enough to catch this, but it
wasn't (back in 2000, anyway). Check the 'Keep together' properties of ALL
your elements and see if one isn't causing this.

Pete