Totals from a sub-report

[Mike Turco]

I had a report today with two sub reports. The main rpt was invoice and the
subs were parts and labor. I had to bring the sub-totals into the form,
etc., to create an invoice with a total price.

What a bunch of crap I had to go through to get this thing to work. The
difficulty was that some reports had parts and no labor, or vica-versa. Any
kind of anything of trying to deal with the null recordset gave me the
#stupid# error in the subtotal fields on the main report.

I ended up doing this elaborate thing where I created two text boxes, each
of which contained the dcount of the recordset of the subforms. Then in the
actual parts and labor subtotal fields I used iif statements referring back
to those hidden text boxes.

This one really got me today! There must be a simple solution to this
problem. Any suggestions?

Mike

[Allen Browne]

Test the HasData property of the report in the subreport control.
Use IIf() to supply zero if it has no data.
If it has data, use Nz() to substitute zero for Null.

Assuming a subreport control named "SubParts", and a text box name
"txtSubTotal" in the subreport, the Control Source of the box on the main
report would look like this:
=IIf([SubParts].[Report].[HasData], Nz([SubParts].[Report].[txtSubTotal],
0), 0)

Set the Format property of that text box to Currency, so Access understands
the intended data type.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Turco" <mi*******@yahoo-no-spam-4-me.com> wrote in message
news:Z91Hd.8270$nt.3718@fed1read06...

I had a report today with two sub reports. The main rpt was invoice and the
subs were parts and labor. I had to bring the sub-totals into the form,
etc., to create an invoice with a total price.

What a bunch of crap I had to go through to get this thing to work. The
difficulty was that some reports had parts and no labor, or vica-versa.
Any kind of anything of trying to deal with the null recordset gave me the
#stupid# error in the subtotal fields on the main report.

I ended up doing this elaborate thing where I created two text boxes, each
of which contained the dcount of the recordset of the subforms. Then in
the actual parts and labor subtotal fields I used iif statements referring
back to those hidden text boxes.

This one really got me today! There must be a simple solution to this
problem. Any suggestions?

Mike