Unbound Text box’s value from a different report

[Lmens]

I have an unbound field in rptMonth whose value is calculated based on total revenue for a month (in rptMonth) divided by total rev. for the year, which is in report rptYear. In the control source I have: =(Sum([APRILREV])/(Reports!rptYear!Sum(REV))) The report asks for parameter value for rptYear.
Please help, what am I doing wrong? Is it possible to bring in values from another report?

Any help given is much appreciated!

Lena.

 

[pbaldy]

It would only be possible if the other report was open at the time (and you'd want to refer to the textbox containing the sum, not summing here).

 

[Lmens]

I tried with the other report opened but it returned “#Error” value.
Here is the formula in the Expression Builder: =Text29.ControlSource/Reports!VmSouTotal!Text20.ControlSource

Your help is appreciated!

 

[pbaldy]

Does this work?

=Text29/Reports!VmSouTotal!Text20

.Value is the default, which is what you want anyway.

 

[Lmens]

No Paul, it did not work. Thanks.

 

[pbaldy]

And both reports are open? No subreports involved here? Can you post the db?

 

[boblarson]

Does this work?

=Text29/Reports!VmSouTotal!Text20

.Value is the default, which is what you want anyway.

I may be off here, but I think, since it is in a control source and referencing other items that the first Text29 needs to be in square brackets:

=[Text29]/Reports!VmSouTotal!Text20

 

[Lmens]

Yes boblarson, I had: =[Text29]/Reports!VmSouTotal!Text20 but did not work.

 

[boblarson]

Well, it was an idea.

 

[Lmens]

Paul, both reports are opened. No subreports involved. The DB has sensitive company info. I don't think I can post it.

 

[pbaldy]

I think your problem is because the value from the other report happens to be zero, and dividing by zero produces an error. Try this:

=IIf([Reports]![VmSouTotal]![Text20]= 0, 0, -[Text29]/[Reports]![VmSouTotal]![Text20])

Some other thoughts. You shouldn't need a query/report for each user. Use a parameter query that gets user input (a combo box on a form for instance, where they choose who they want the report based on), and filters itself based on that. Or have no parameter in the query and use this method:

http://www.baldyweb.com/wherecondition.htm

Spaces and symbols in your object names are not a good idea. It's a little unusual to have one report dependent on another. You run the risk of the user closing the base report first and then trying to print the second or something, which will cause the formulas that depend on the first report to error. I'd look for a way to generate the number on the same report if possible. A week from now neither you nor anybody else will know what Text20 is. I'd give it (and all the others) a meaningful name, like txtSumApril or something like that. And speaking of April, it's also unusual to have hard-coded dates. I'd try to come up with a more dynamic method, though I say that without having really reviewed your data.

 

[Lmens]

Thanks for your advice! The value is zero because I deleted the tables with April and May numbers to reduce the file size to be able to send it. Otherwise it will never be zero. I agree with you on the hard-coded dates but have no clue on how to do that!