Expression Builder; embedded data from other reports in Summary re

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi. I think I have a syntax problem I’d appreciate some help with. I have
several reports and I’d like to use the data from them to populate a Summary
report. I’m using Expression Builder which I think is excellent (for me) as
it gives very clear paths to Collections and objects.

The data I’m trying to add to the Summary report has been built by summing
rows in another report and hence the data is not a table entry. I make a BIG
assumption here that as Expression Builder lets you see all your other
report, then embedding data from each of those other reports to my Summary
Report is allowed – happy to be corrected on that if it isn’t!

This is my syntax which I’m getting the familiar #name? error message on:
=Reports![Weekly CASH In Unrestricted]![WkCashInUnrTOTAL]

I think this explains itself, but the final part [WkCashInUnrTOTAL] is the
unbound field (control source) from another / different report that I’m
trying to embed in my Summary Report.
 
As far as I know, the only way you ***might** even be able do this is to have
the other report open. And obviously the control couldn't repeat in the
report or you would have NO idea which value you would get from the possible
multiple values.

You could add the report(s) to your summary report and get data from the
sub-reports. You could set the visible property of the sub-report to No.

All that said - no of the above is a good solution.

A better solution would be to find a way to calculate the values you need in
the summary report. You might be able to do that with the VBA domain
functions (DSum, DAvg, etc) or you might be able to do it in the source for
the summary report. Without a lot more detail, it is hard to propose a
detailed solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Ok guys thanks for the advice. As I said, the working assumption was that
as you were ‘presented’ with the option to pick a report off the Expression
Builder, then that meant it was a valid action. I’ll take your advice and
look at doing a query or perhaps re-doing the expression I the summary.
Thanks for educating me – appreciated.

--
Peter


John Spencer said:
As far as I know, the only way you ***might** even be able do this is to have
the other report open. And obviously the control couldn't repeat in the
report or you would have NO idea which value you would get from the possible
multiple values.

You could add the report(s) to your summary report and get data from the
sub-reports. You could set the visible property of the sub-report to No.

All that said - no of the above is a good solution.

A better solution would be to find a way to calculate the values you need in
the summary report. You might be able to do that with the VBA domain
functions (DSum, DAvg, etc) or you might be able to do it in the source for
the summary report. Without a lot more detail, it is hard to propose a
detailed solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi. I think I have a syntax problem I’d appreciate some help with. I have
several reports and I’d like to use the data from them to populate a Summary
report. I’m using Expression Builder which I think is excellent (for me) as
it gives very clear paths to Collections and objects.

The data I’m trying to add to the Summary report has been built by summing
rows in another report and hence the data is not a table entry. I make a BIG
assumption here that as Expression Builder lets you see all your other
report, then embedding data from each of those other reports to my Summary
Report is allowed – happy to be corrected on that if it isn’t!

This is my syntax which I’m getting the familiar #name? error message on:
=Reports![Weekly CASH In Unrestricted]![WkCashInUnrTOTAL]

I think this explains itself, but the final part [WkCashInUnrTOTAL] is the
unbound field (control source) from another / different report that I’m
trying to embed in my Summary Report.
Click to expand...
.
Click to expand...
 
Ok guys thanks for the advice. As I said, the working assumption was that
as you were ‘presented’ with the option to pick a report off the Expression
Builder, then that meant it was a valid action. I’ll take your advice and
look at doing a query or perhaps re-doing the expression I the summary.
Thanks for educating me – appreciated.

--
Peter


Marshall Barton said:
Peter said:
Hi. I think I have a syntax problem I’d appreciate some help with. I have
several reports and I’d like to use the data from them to populate a Summary
report. I’m using Expression Builder which I think is excellent (for me) as
it gives very clear paths to Collections and objects.

The data I’m trying to add to the Summary report has been built by summing
rows in another report and hence the data is not a table entry. I make a BIG
assumption here that as Expression Builder lets you see all your other
report, then embedding data from each of those other reports to my Summary
Report is allowed – happy to be corrected on that if it isn’t!

This is my syntax which I’m getting the familiar #name? error message on:
=Reports![Weekly CASH In Unrestricted]![WkCashInUnrTOTAL]

I think this explains itself, but the final part [WkCashInUnrTOTAL] is the
unbound field (control source) from another / different report that I’m
trying to embed in my Summary Report.
Click to expand...


That "BIG assumption" would only work if the other reports
are open and even then only if the text boxes you refer to
are unique in the other report (i.e. they only appear once,
not in a detail of a multiple record report).

To create a summary report, you should first create a query
that extracts and/or calculates the summary data, then use
the query as the record source of the summary report.
Click to expand...
 
Back
Top