Report will not generate

[John M via AccessMonster.com]

New member here. I'm attempting to create a report from a query. The query is
built on two crosstabs and 3 small select queries, all joined by
"InspectorName". The query itself produces the exact desired result, and runs
beautifully. However, when I try to create a Report, Access 2003 (sp1) stops
responding. Task Manager end process is my only choice.

Default printer is set. Driver is current. Parameters in both crosstab
queries are correct. Column headings properties are correct. Everything
appears "set", as indicated by the verified correctedness of the query.

I also kept open a table linked to the backend...as someone suggested to
speed things up.

The main table is a backend link. No problems running less complex queries
and creating reports based on the same table. Any advice is appreciated. I'd
rather avoid a maketable approach.

 

[Allen Browne]

There are several things that could cause a report to format slowly. It can
be simple things such as a text box bound to:
=[Page] of [Pages]
To get the value to show for Pages, Access has to format the entire report,
so it takes ages to see the first page.

Until you get it working, you might like to also turn off any CanShrink or
CanGrow properties. Fixed height sections should be easier for Access to fit
to the page. Also comment out any code in the report's events until you get
it working.

Crosstabs have to run to completion before Access knows the field names the
report needs. That can also be a bottleneck. Presumably you do know the
fields ahead of time, if you have controls on the report bound to those
fields, so could you put the list of names into the Column Headings property
of the crosstab?

You have already handled several of the important bottlenecks, so you
probably have Name AutoCorrect turned off, and SubdatasheetName set to
[None] for your tables. If not, these are discussed in the Tony Toews'
Performance FAQ:
http://www.granite.ab.ca/access/performancefaq.htm

If that doesn't help, tell us more about the SQL statements.

 

[John M via AccessMonster.com]

Thanks for your response. Some of these things cannot be addressed until the
report actually generates. With that in mind, I again attempted to create the
report directly from the query...only this time I did not kill it via task
manager (which again reported a "Not Responding" message). Eventually the
report generated (8 minutes), and task manager changed from "Not Responding"
to "Running".

The initial query has date criteria, which I failed to mention earlier. When
I selected a 1 year span, the report simply would not work. This time I
selected 1 month and it worked, finally allowing me to see the report, make
design changes, and save. And after saving and retrying with 1 year, it
worked again. I then made some of the changes you suggested. It takes a
minute to appear, but I can live with it.

All things considered, and for whatever the reasons, it works and I am happy.

Perhaps my patience was an additional issue, combined with network traffic
(it's currently early morning). I appreciate your help. This forum has been
quite helpful, especially to an on-the-job learner such as myself.

 

[Allen Browne]

Excellent, John.

There are other things that might help, such as:
1. Indexing.
Open the table in design view, and select the Date/Time field.
In the lower pane, set the Indexed property to: Yes (Dupes ok.)

2. How date criteria are applied.
This is a long story, but we need to ensure the right data type for the
parameters, and specify values that will let Access use the index. For
example, this should work for this year:
Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date(), 12,
31)