Adding with Subs

  • Thread starter Thread starter Bruce Rodtnick
  • Start date Start date
B

Bruce Rodtnick

I have a report that has several subReports. I need to add the results
from each subReport and I have been able to do that unless there is
nothing in one of the subReports.

It seems that when there is not anything in the subReport, the query
doesn't even know it's there. the Nz function has not worked. This is
my code:

=Nz([rptsubFacility].Report!txtFacilityTotal,0)+Nz([subPersonnel].Report!txtPersonnelTotal,0)+Nz([txtTravelTotal],0)

When subPersonnel does not have anything in it, it does not show on the
report. Any ideas?

Bruce Rodtnick
 
Bruce,

This is from memory so you may need to fix up the syntax.
iif(iserror([reports]![reportname]![subreportname]!
[controlname])=True,0,([reports]![reportname]!
[subreportname]![controlname])

If subreport is not there this should return zero and keep
your calculation valid. This is one way I remember doing
this (but probably not the easiest or best way).

HTH,

Terry
 
You can use the HAS DATA property of the subreport to determine this. Example
from my memory, so the syntax could be wrong.

= IIF([rptsubFacility].Report!txtFacilityTotal.HasData =
True,[rptsubFacility].Report!txtFacilityTotal,0) +
IIF([subPersonnel].Report!txtPersonnelTotal.HasData = True,
[subPersonnel].Report!txtPersonnelTotal,0) +
Nz([txtTravelTotal],0)
 
Thanks, guys. Your memory wasn't quite right, but it got me on the
right track. I did some study and came up with:

=IIf([rptsubFacility].[Report].[HasData],[rptsubFacility]!txtFacilityTotal,0)+IIf([subPersonnel].[Report].[HasData],[subPersonnel]!txtPersonnelTotal,0)+Nz([txtTravelTotal],0)

and it works. Thanks agian.

B

John Spencer (MVP) said:
You can use the HAS DATA property of the subreport to determine this. Example
from my memory, so the syntax could be wrong.

= IIF([rptsubFacility].Report!txtFacilityTotal.HasData =
True,[rptsubFacility].Report!txtFacilityTotal,0) +
IIF([subPersonnel].Report!txtPersonnelTotal.HasData = True,
[subPersonnel].Report!txtPersonnelTotal,0) +
Nz([txtTravelTotal],0)

Bruce said:
I have a report that has several subReports. I need to add the results
from each subReport and I have been able to do that unless there is
nothing in one of the subReports.

It seems that when there is not anything in the subReport, the query
doesn't even know it's there. the Nz function has not worked. This is
my code:

=Nz([rptsubFacility].Report!txtFacilityTotal,0)+Nz([subPersonnel].Report!txtPersonnelTotal,0)+Nz([txtTravelTotal],0)

When subPersonnel does not have anything in it, it does not show on the
report. Any ideas?

Bruce Rodtnick
Click to expand...
Click to expand...
 
Back
Top