Totals on sub reports

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a report which is based on a query which is based on Table A and on
it a subreport based on a query that is based on Table B. The tables are
linked on a field named VFUNbr. There will always be records for that field
in Table A but not always in Table B. The report shows all Table A records.
In the query for the subreport I am counting the value of a tick box using
this formula
=Sum(Abs([Arrest])) in a field called Total Arrests
On the main report I am showing this field as
=[Active FilesA].Report![Total Arrests]
and a running total with this formula
=IIf(Val([Total Arrests])=0,0,Val([Total Arrests])) with the running sum
property set to overall
Everything works fine if both tables have equivalent VFUNbr fields. However
if there is not a value in Table B for the field VFUNbr the values of Total
Arrests on the main form are shown as #Error Where am I going wrong?
TIA
 
Generically speaking, if you have a text box "txtTotal" on a subreport
"srptB" and you want this to display on the main report, you can use:
=IIf(srptB.Report.HasData, srptB.Report!txtTotal, 0)
If there is no data in the subreport, the text box will display 0.
 
Sorry ignore last post forgot the= sign (eyes getting tired after a hard day
and missed it !!!!!)
Tony
Duane Hookom said:
Generically speaking, if you have a text box "txtTotal" on a subreport
"srptB" and you want this to display on the main report, you can use:
=IIf(srptB.Report.HasData, srptB.Report!txtTotal, 0)
If there is no data in the subreport, the text box will display 0.

--
Duane Hookom
MS Access MVP


Tony Williams said:
I have a report which is based on a query which is based on Table A and on
it a subreport based on a query that is based on Table B. The tables are
linked on a field named VFUNbr. There will always be records for that field
in Table A but not always in Table B. The report shows all Table A records.
In the query for the subreport I am counting the value of a tick box using
this formula
=Sum(Abs([Arrest])) in a field called Total Arrests
On the main report I am showing this field as
=[Active FilesA].Report![Total Arrests]
and a running total with this formula
=IIf(Val([Total Arrests])=0,0,Val([Total Arrests])) with the running sum
property set to overall
Everything works fine if both tables have equivalent VFUNbr fields. However
if there is not a value in Table B for the field VFUNbr the values of Total
Arrests on the main form are shown as #Error Where am I going wrong?
TIA
Click to expand...
Click to expand...
 
Back
Top