Blank subreports creates errors 1

[JR2913]

I would appreciate some help with a little problem that has occurred with a financial report (a statement for an individual service user) I have created.

The main report shows payments made.

PROBLEM 1 - if there are no payments I cannot get the textbox that calculates the total payments (in this case 0) to be displayed.

There are two subreports within the main report, each providing a total of different types of service charges for the individual and generated from temporary tables which relate to the services used by the individual.

PROBLEM 2 - If either of the temporary tables is empty (because the user hasn't used that particular type of service) the subreport doesn't appear, because there is no data. I therefore have no total to display.

PROBLEM 3 - Because of problems 1 and/or 2 I can't get the balance to display in the textbox at the foot of the report, because there is missing data from the subreports or from the total payments (if it is 0).

I'm not sure that I have explained this very clearly! Sometimes you get too close to something and can't see the wood for the trees.

However, any advice you can give me that would help resolve these problems would be much appreciated.

Thanks in advance for your time.

John R

 

[dhookom]

You will need to use the HasData property of the subreport control. For instance if your subreport control name is "srptMySub" and you want to use a total from the subreport, try:
=IIf(srptMySub.Report.HasData, srptMySub.Report!txtTotalExp, 0)

This will show 0 if there are no records in the subreport.

Another option (I prefer) is to create totals queries from the record sources of your subreports. Combine these totals queries with into your report's record source joining on the field(s) you would have used to Link Master/Child.

 

[JR2913]

Thanks

I have now solved Problem 1.

However, I can't find any reference to the HasData function. I'm not sure if my syntax is right or whether this doesn't exist in Access 2K. I have found On No Data in the Events, but can't get this to work in this context, either! The only thing it did succeed in doing was drawing the border round the subreport area on the main report!

I'm not sure I understand fully the use of Master/Child Links.

Ever had that feeling you're digging a deeper hole and gradually sinking in to it??

I have even tried using a nested IIf statement in the final calculated text box on the main report but it falls over at that point. At the moment the syntax is:

=IIf(DCount("[transactionid]","tblAdultTransactions","[Adult ID] = " & Forms!frmReports!Combo89)=0,"-£" & DSum("[Cost]","tbltmpAdultCharges&quot+DSum("[Charge]","qryAdultHourlyCharges&quot,Sum([Receipt Amount])-(DSum("[Cost]","tbltmpAdultCharges&quot+DSum("Charge","qryAdultHourlyCharges&quot))

This resolves the issue of the payments being blank but not not if the AdultCharges or AdultHourlyCharges are blank.

As I understand it, as this final balance item is in the Report Footer it isn't possible to make use of values from text boxes elsewhere in the report(s) but values have to be recalculated.

Any more help you can give would be much appreciated.

 

[JR2913]

OK

Have found HasData at last, but can only get it to work in the subreport when I run it separately from the main report and in a separate textbox. Clearly doing something wrong, but can't track down exactly what.

Any ideas, please?

Many thanks

John R

 

[NeilRP]

dhookom,

HasData property fixed a very similar issue for me.
Have a star!

Cheers

 

[glauco]

This is not my idea but i think i can make it clear. Whe dhookom writes:

Another option (I prefer) is to create totals queries from the record sources of your subreports. Combine these totals queries with into your report's record source joining on the field(s) you would have used to Link Master/Child.

* He's telling you to sum from a query, not from the subreport itself, this way the result will be zero in case there´s no data in your subreport, later he states you use should link this "summary queries" to you data source as you would do for your subreport, that's what i undestand i think i have done in other environment.