Report to show sums from crosstabs if one sum is blank

[Killdozer]

I have 8 different crosstab queries showing the sum of the "total" from the quert from which they were made.
I then made one Query combining the 8 crosstabs into one query.
I made a report of the Combined query. With a text box in the report footer showing the sum.
All the calcs work fine unless one of the queries is null, which stops the total in the reports footer.
I tried using NZ() to give me a zero but either I'm using it in the wrong place, or using it incorrectly
Can somone shed some light on this.
Killdozer

 

[apr pillai]

I had this kind of problems in my projects. I will explain what I did.

1. Create a Temporary Report Table with all the required fields to accommodate all field values from all crosstab queries.
2. Create a Report Table with the same structure of the temporary Table.
3. Append each cross-tab query output into the Temporary Table
4. Create a Total Query using the Temporary Report Table as Source.
5. Append the Total Query Output into the Report Table.

All these actions can be sequenced and automated through macros.
When these actions are to be repeated then create Delete Queries to remove the contents of earlier runs from both Report Tables. Insert these Queries at the begining of the macro.

If one or more crosstab Queries turned out empty no problems for the Report. Add the Report opening statement at the end of the Macro so that the Report will open automatically after creating the Report Table.

Run the macro from a Button click on a Form or from a Menu option etc.