help with formulas in a report

[DeeJar]

Please help with two sets of formulas. I have a four page report dealing with survey results. On the last page I am doing a comparison of 2004 and 2005 results.

The formula in 2005 that is not working is:

=sum([ea]+[va]+[ga])/[c1]

all referenced controls are ea, va, etc. are on this report. I think I have omitted a comma, an operator or something that is causing this not to work??? Help.

Right, next to this column of information, I need to create the same formulas referencing the 2004 reports and/or tables also in this data base. When I use the expression builder it created the following:

=[Reports]![Reading First 2004 Overall Report]![c1]
which gives me the #Name? error.

Any suggestions would be appreciated.

 

[PCLewis]

The second error could be due to the first error. Assuming the report is open and displaying a value for C1, your second expression should be fine. If the report is not open, you will get that error because as far as VB is concerned, there is no instance of [c1].

The first error will be due to you trying to use an aggregate function (Sum) to try and do something it's not meant for. YOu say this is on the last page, so I assume you are putting it in the Report footer and expected the Sum clause to sum all the records in the preceeding pages for you?

Because what you are trying to do is an important (essential!!) feature of reporting, Access gives you the "Running Sum" property of a Text Box in a report.

One way to do this is to place a hidden text box in the detail section with the expression = [ea] + [va] + [ga]. Now set that text box to have the Running Sum property set to Over All.

In the report footer you can now have another text box with the expression = [Text1] / [C1]

Obviously using the name of the text box you used.

Another way to do it is using DSum in the footer but in this case I see no reason to do a second table scan just for something the report engine can do "for free"

Cheers

 

[dhookom]

I would absolutely never add a text box in one report that refers to a value in another report. Values displayed in reports come from some "source data values". Always go back to the source data values, not to an object where you expect them to be displayed.

PCLewis is correct regarding the running sums. You can't sum a control from one section to another. You can sum expressions that involve fields in the report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DeeJar]

PCLewis and dhookom
Thank you so much for your responses. I think I have this worked out thanks to help from both or you. One more thing has popped up.

The "hidden" totals I now have and the resulting percentages work on two of the totals. But c, d, and e are wrong. What could cause this? I have retyped and redone with the expression builder several times, but continue to get the wrong answers. See below. c1=794

[ea]+[va]+[ga] =781 [2005a]/[c1] =98
[eb]+[vb]+[gb] =746 [2005b]/[c1] =95
[ec]+[vc]+[gc] =685 [2005c]/[c1] =27
[ed]+[vd]+[gd] =724 [2005d]/[c1] =18
[ee]+[ve]+[ge] =545 [2005e]/[c1] =64

Again, thank you both very much for your quick response. I am going to sleep on this. LOL

DJ

 

[dhookom]

I would need to see the data. You expressions don't tell us much about your data source values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]