A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Subreport Expressions



 
 
Thread Tools Display Modes
  #1  
Old November 24th, 2005, 10:23 AM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default Subreport Expressions

I have a subreport called subrpt_abc
Its embedded in a report called rpt_main.

The subreport has a calculated field called TotalACost.

The main report also has a calculated field called TotalPanelCost.

I wanted to be able to add these two values together. It only seems to work
when I create a Grand Total field in the subreport only.
However the expression I used does not work. I used:
=Reports!rpt_main!TotalPanelCost!+TotalACost

Note: when my mouse cursor exits the Control source field, the expression
all of a sudden has brackets: ie
=[Reports]![rpt_main]![TotalPanelCost]!+[TotalACost]
I think these brackets cause the error in giving me the required total value
amount.
Why are these brackets appearing?


  #2  
Old November 24th, 2005, 11:11 AM posted to microsoft.public.access.reports
external usenet poster
 
Posts: n/a
Default Subreport Expressions

Presumably TotalACost is a text box in the Report Footer section of the
subreport.

Put a text box on the main report, and set its Control Source to:
=IIf([subrpt_abc].[Report].[HasData],
Nz([subrpt_abc].[Report].[TotalACost],0), 0)

When there are no matching records in the subreport, nothing shows and so
referring to the non-existent text box in the subreport causes an error. The
expression above solves that by testing the HasData property of the report
in the subreport control.

It is possible that the total could be Null, so it solves that with Nz().

Access can misunderstand the data type of calculated text boxes. To solve
that, set the Format of these text boxes to Currency (or General Number or
something.)

If you still get #Error, there is a problem with the name. Check that:
a) the calculated text boxes do not have the same Name as one of the fields,
and
b) your expression uses the name of the subreport control (which may be
different from the name of the report it contains, i.e. its SourceObject.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deiopajw" wrote in message
u...
I have a subreport called subrpt_abc
Its embedded in a report called rpt_main.

The subreport has a calculated field called TotalACost.

The main report also has a calculated field called TotalPanelCost.

I wanted to be able to add these two values together. It only seems to
work
when I create a Grand Total field in the subreport only.
However the expression I used does not work. I used:
=Reports!rpt_main!TotalPanelCost!+TotalACost

Note: when my mouse cursor exits the Control source field, the expression
all of a sudden has brackets: ie
=[Reports]![rpt_main]![TotalPanelCost]!+[TotalACost]
I think these brackets cause the error in giving me the required total
value
amount.
Why are these brackets appearing?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Parameterizing subreports Jack Setting Up & Running Reports 8 October 21st, 2005 12:00 PM
Printing Subreport without data CCross Setting Up & Running Reports 2 June 1st, 2005 06:04 PM
repeating records in a subreport Paul James Setting Up & Running Reports 2 February 2nd, 2005 07:11 PM
SubReport No Data printing problem Tony_VBACoder Setting Up & Running Reports 1 August 9th, 2004 10:10 PM
Problems totalling subreport data on main report (A-2002) Marshall Barton Setting Up & Running Reports 0 June 23rd, 2004 08:01 PM


All times are GMT +1. The time now is 02:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.