Calculated control in a report

[MNNovice]

Hi:

I have a report that's based on a query. This report has two sub reports which are also based on two separate queries.

Main report: rptECHO
Sub reports: srptEchoAP & srptEchoPay

srptEchoAP has all data pertaining to invoice payments for all ECHOs. (ECHO is a process through which expenses for a grant get reimbursed.) Similarly, srptEchoPay has data pertaining to payroll expenses.

I have sub total of AP and sub total of Payroll expenses on the respective sub reports. I need to show the Total ECHO expenses (AP + Payroll) at the bottom of the rptECHO.

How do I do it? Any help is much appreciated.

Thanks.

[NeoPa]

What are the names of the controls on your subreports?

What are the names of your subreport controls on the main report (You've already supplied the names of the reports themselves, contained within. This is not the question)?

[MNNovice]

Sub Report: srptEchoPayDeta il
Control Name: txtPayAmount

Sub Report: srptEchoAPDetai l
Control Name: txtAPNet

I need the total on rptECHO (main report)
txtAPNet + txtPayAmount

I tried this on the report footer. Didn't work. Perhaps somehow I need to refer to these text boxes to the sub report. This I don't know how to do.

Thanks.

[NeoPa]

Before I get a chance to look at this properly, you may want to take a look through Referring to Items on a Sub-Form. If you remember that most references to subforms should work very similarly for subreports, you should be able to work out the referencing.

If not then I'll try to take a proper look later to see if I can get it to work.

[NeoPa]

I've just checked over your answer in more detail & I notice you haven't answered my questions. I expect you haven't understood clearly, but please read again carefully and provide the requested information.

[MNNovice]

What are the names of the controls on your subreports?

Sub Report: srptEchoPayDeta il
Control Name: txtPayAmount (Text67 on the version of DB you have)

Sub Report: srptEchoAPDetai l
Control Name: txtAPNet (Text71 on the version of DB you have)

What are the names of your subreport controls on the main report (You've already supplied the names of the reports themselves, contained within. This is not the question)?

I don't have created a control (to calculate the total) for the subreport on the main report - yet (hope I understood your question).

If not, let me know.

[MNNovice]

I will read it and shall let you know. Thanks.

[MNNovice]

NeoPa:

I found this write up on the net and it solved my problem. However I succeeded only with the STEP 1. I would like to try the NZ formula part of it, may be tomorrow.

The controls of my subform on the main form are: PayExpenes and APExpenses

Thanks and have a nice evening.


Bring the total from a subreport back onto the main report
Your subreport has a total at the end - a text box in the Report Footer section, with a Control Source like this:

=Sum([Amount])

Now, how do you pass that total back to the the main report?
Stage 1
If the subreport is called Sub1, and the text box is txtTotal, put the text box on your main report, and start with this Control Source:

=[Sub1].[Report].[txtTotal]

Stage 2
Check that it works. It should do if there are records in the subreport. If not, you get #Error. To avoid that, test the HasData property, like this:

=IIf([Sub1].[Report].[HasData], [Sub1].[Report].[txtTotal], 0)

Stage 3
The subreport total could be Null, so you might like to use Nz() to convert that case to zero also:

=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtTotal], 0), 0)

[NeoPa]

What are the names of your subreport controls on the main report (You've already supplied the names of the reports themselves, contained within. This is not the question)?

I don't have created a control (to calculate the total) for the subreport on the main report - yet (hope I understood your question).

If not, let me know.

When putting a subreport into another (main) report, the subreport is actually defined as a report. What makes it a subreport is that a control (a subreport control) is created on the main report. This control contains a report, which is what you generally refer to as the subreport. Technically however, this is not the subreport. The subreport is the control that contains the embedded report. The names of these (subform) controls are what I'm after.

[NeoPa]

I found this write up on the net and it solved my problem. However I succeeded only with the STEP 1. I would like to try the NZ formula part of it, may be tomorrow.

This is fundamentally what I was trying to get at (if you check the link I provided in post #4). Notice the code refers to the subreport control ([Sub1]).

What you need to do with this process is firstly to work out the names of all the elements you want to deal with. This I was trying to help with, with my request for names etc.

[NeoPa]

I need to ask why [Text67] is in ECHOID Footer, instead of the Report Footer? Do you want only the last ECHOID value?

PS. One of your subreport controls is called [A/P Expenses] if that helps.

[NeoPa]

It seems the other is called [rptEchoPaySum]. I may have run out of time for tonight though. I'll look again when I can.

[MNNovice]

NeoPa:

I got this issue resolved. Don't spend any of your time on this matter. Thanks.

[MNNovice]

I have updated the DB since the last copy that was posted. I learned about the control of a sub report once I read the article I posted on Posting #8.

"A/P Expenses" was rejected by ACCESS so I changed it to "APExpenses "

[NeoPa]

Thanks for updating M.

As for the names of your controls, I recommend finding some form of consistent naming convention so that you can always understand from the name what the item is about. Also, that you can know what the name should be simply by knowing what the control is for.

Does that make sense?