Grand total from subreport totals

[Guest]

G'day. I've seen other threads along the lines of this question and adapted
their answers with no success, so I'm going to bother you all with my own
dilemma. I'm new to subreporting and report totals and have managed to add a
total to each individual report (there are 3) using the ControlSource field.
Now I need a grand total for all the subtotals in the report. I have tried
using the labels for these (Total) like so:

=((TOTAL_BED_COST!Total)+
(PATIENT_TREATMENTS!Total)+
(PATIENT_MEDICAL_ITEMS!Total))

But this results in 0

So am I on thr right track? All I need is to be able to get a grand total
from all the subreport totals. Thanks for any help you can give!

 

[Marshall Barton]

G'day. I've seen other threads along the lines of this question and adapted
their answers with no success, so I'm going to bother you all with my own
dilemma. I'm new to subreporting and report totals and have managed to add a
total to each individual report (there are 3) using the ControlSource field.
Now I need a grand total for all the subtotals in the report. I have tried
using the labels for these (Total) like so:

=((TOTAL_BED_COST!Total)+
(PATIENT_TREATMENTS!Total)+
(PATIENT_MEDICAL_ITEMS!Total))

But this results in 0

So am I on thr right track? All I need is to be able to get a grand total
from all the subreport totals. Thanks for any help you can give!

If the subreports are all in the same section along with the
text box using your expression, then your expression should
work fepending on your version of Access. Access 2003 is
more strict about that kind of reference so it's a good idea
to be a little more formal:

=((TOTAL_BED_COST.REPORT!Total)+
(PATIENT_TREATMENTS.REPORT!Total)+
(PATIENT_MEDICAL_ITEMS.REPORT!Total))

Note that if there's any chance that a subreport might not
have any data, then you have to test for that situation:

=IIf(TOTAL_BED_COST.REPORT.HasData,
TOTAL_BED_COST.REPORT!Total, 0) +
IIf(PATIENT_TREATMENTS.REPORT.HasData,
PATIENT_TREATMENTS.REPORT!Total, 0) +
IIf(PATIENT_MEDICAL_ITEMS.REPORT.HasData,
PATIENT_MEDICAL_ITEMS.REPORT!Total)

 

[Guest]

Thanks for the quick reply.

I'm using Access 2003 so I tried the first cleaner section of code and that
produced #Error which is I guess progress, as before I just go 0.

The report is based off a query on a patient. Although the patient I'm
testing with has data in all subreports, there re patients that have no
medical_items. So I tried the next section of code and this too produced an
error. Now I am stumped...

Sorry for this, but thanks again if you can think of how to fix things.

 

[Guest]

Oops just to update I found the IsNull function and added that successfully
to the MEDICAL_ITEMS report, however the grand total is still an error

Happy pondering.

 

[Marshall Barton]

Oops just to update I found the IsNull function and added that successfully
to the MEDICAL_ITEMS report, however the grand total is still an error

Only thing that comes to mind is that somehow the subreport
totals are text instead of numbers. Do the subreports
display the total correctly?

What are the expressions in the subreport Ttoals text boxes?

 

[Guest]

Bingo! You thought right. The subreport totals displayed perfectly as
currency yet I had left the textbox names to their defaults and only changed
the captions! I had thought of this before but must have only changed the
label names. How embarrassing

Thanks for all your help. I've learnt things about Access I didn't know it
was capable of. It's just a shame I always look for the most difficult
solution hehe.

Cheers

Gemma

 

[Marshall Barton]

Bingo! You thought right. The subreport totals displayed perfectly as
currency yet I had left the textbox names to their defaults and only changed
the captions! I had thought of this before but must have only changed the
label names. How embarrassing

Thanks for all your help. I've learnt things about Access I didn't know it
was capable of. It's just a shame I always look for the most difficult
solution hehe.

Well that's a new one on me ;-)

I sure am glad you figured it out from my obscure comment,
because I don't think I would ever have guessed that was the
problem. I'll have to keep that possibiliy in mind in case
it ever happens again <gdr>