Formula in Report (Access 2000)

Topic

New Reply

Hi,

I have a main report and a subreport. In the subreport I have a details section and then a details footer with a Sum formula for some of the columns in the details section. I would like to add on the main report a grand total of the numbers included in the subreport. I have used the following formula: =Sum([RptSubAdLog].[Report]![Company_Pledge]). The RptSubAdLog is the subreport and the Company Pledge is one of the controls in the subreport that I want a grand total of in the Main report footer. This is not working. No numbers or errors come up at all. Any suggestions. Thank you.

Dorothy

Reply | Quote

Replies

In general, you can’t use Sum to add the contents of controls, only to add the values of fields and expressions. Is the subreport repeated on the main report? In that case, you will have to find another way to calculate the sum, perhaps with DSum. If the subreport is displayed only once, you can calculate the sum in the report footer of the subreport, and refer to that in the main report.

Reply | Quote

Hi Hans,

I am incredibly lost and have tried a number of things. Can you explain the Dsum and how I would use it? These are the elements involved:
A subreport name RptSubAdLog
This subreport has detail section with Company_Pledge (currency)
The subreport has a detail footer that sums the Company Pledge: =Sum[Company_Pledge]
There are multiple companies listed in the subreport

Can I reference the =Sum[Company_Pledge] box on the main report footer? if so, as I am really lost, how would I do this specifically? From your last post it sounds like this is not possible which brings us to Dsum. Again, I find help to be no help but it may be because I am so ignorant in this area.

Thanks for any help you can give me.

Dorothy

Reply | Quote

1. If you open the main report, are multiple copies of the subreport displayed, or just one? (If just one, this single copy can still display multiple companies within the subreport!)

2. How is the subreport linked to the main report? To check this, open the main report in design view, and click ONCE on the subreport to select it. Activate the Data tab of the Properties window. What are the Link Master Fields and Link Child Fields properties?

Reply | Quote

Hi Hans,

When I open the report in design view there is just one subreport. In the regular view the subreport displays once under a Lead Company, which is part of the Main report, but there are many Lead companies, each with a subreport listed with company info. The subreport is link as follows: Link child fields – Lead_Name. Link Master fields- Lead_Name.

Dorothy

Reply | Quote

Thanks for this description. MSKB article ACC2000: How to Display and Total Subtotals from Subreports describes several methods you can use. Perhaps the easiest is the method described under “Using a Second Subreport”. In your situation, it would go like this:

– Make sure the report is closed.
– Open the RptSubAdLog (sub)report in design view.
– Select File | Save As… to make a copy. Name the copy RptSubAdLogSum.
– Delete all controls from the Detail section, then set the height of the Detail section to 0.
– Close and save RptSubAdLogSum.
– Open the main report in design view.
– Add RptSubAdLogSum as a subreport to the report footer section of the main report.
– Make sure that this subreport is NOT linked to the main report: the Link Child Fields and Link Master Fields properties must be blank.

This method will not work correctly if you open the main report from a form with an additional where-condition imposed, but it should be OK otherwise. Post back if you have problems.

Reply | Quote

Hi Hans,

IT WORKED!!! Thank you so much for your help. I did not imagine when I set up this report using a subreport it would take much more than a simple formula to get grand totals. As usual, a very good learning experience. Take care and probably will be seeing me again soon.

Dorothy

Reply | Quote

Hi Hans,

IT WORKED!!! Thank you so much for your help. I did not imagine when I set up this report using a subreport it would take much more than a simple formula to get grand totals. As usual, a very good learning experience. Take care and probably will be seeing me again soon.

Dorothy

Reply | Quote

Thanks for this description. MSKB article ACC2000: How to Display and Total Subtotals from Subreports describes several methods you can use. Perhaps the easiest is the method described under “Using a Second Subreport”. In your situation, it would go like this:

– Make sure the report is closed.
– Open the RptSubAdLog (sub)report in design view.
– Select File | Save As… to make a copy. Name the copy RptSubAdLogSum.
– Delete all controls from the Detail section, then set the height of the Detail section to 0.
– Close and save RptSubAdLogSum.
– Open the main report in design view.
– Add RptSubAdLogSum as a subreport to the report footer section of the main report.
– Make sure that this subreport is NOT linked to the main report: the Link Child Fields and Link Master Fields properties must be blank.

This method will not work correctly if you open the main report from a form with an additional where-condition imposed, but it should be OK otherwise. Post back if you have problems.

Reply | Quote

Hi Hans,

When I open the report in design view there is just one subreport. In the regular view the subreport displays once under a Lead Company, which is part of the Main report, but there are many Lead companies, each with a subreport listed with company info. The subreport is link as follows: Link child fields – Lead_Name. Link Master fields- Lead_Name.

Dorothy

Reply | Quote

1. If you open the main report, are multiple copies of the subreport displayed, or just one? (If just one, this single copy can still display multiple companies within the subreport!)

2. How is the subreport linked to the main report? To check this, open the main report in design view, and click ONCE on the subreport to select it. Activate the Data tab of the Properties window. What are the Link Master Fields and Link Child Fields properties?

Reply | Quote

Hi Hans,

I am incredibly lost and have tried a number of things. Can you explain the Dsum and how I would use it? These are the elements involved:
A subreport name RptSubAdLog
This subreport has detail section with Company_Pledge (currency)
The subreport has a detail footer that sums the Company Pledge: =Sum[Company_Pledge]
There are multiple companies listed in the subreport

Can I reference the =Sum[Company_Pledge] box on the main report footer? if so, as I am really lost, how would I do this specifically? From your last post it sounds like this is not possible which brings us to Dsum. Again, I find help to be no help but it may be because I am so ignorant in this area.

Thanks for any help you can give me.

Dorothy

Reply | Quote

In general, you can’t use Sum to add the contents of controls, only to add the values of fields and expressions. Is the subreport repeated on the main report? In that case, you will have to find another way to calculate the sum, perhaps with DSum. If the subreport is displayed only once, you can calculate the sum in the report footer of the subreport, and refer to that in the main report.

Reply | Quote