Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

subform value

Posted on 2012-12-20
4
221 Views
Last Modified: 2013-01-06
I have a report called Charges_Rpt that contain two subreports

SRpt_supplies
Srpt_services

each one of the subreport has total charges.

total_supplies
total_services

 what I have the charges_rpt is a filed of grand total from both subreport. it works good when I have value in both sub reports but when one of them has blank data, the subreport does not show but the sum in the charge report would give me error

Grand_Total
0
Comment
Question by:the_Apple
  • 2
4 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38711791
Use the NZ function in whatever field could have a null value.  For example:
Nz([Amount],0)
The above will return a zero whenver a field has no value.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 38712007
Depending on your report design, (Grouped Report with GT in the group footer)
You can also use IsError() for the missing (no sum) subreport

The syntax can get a little intimidating, but it basically says if the subreport contains no data, it may not display, If that is the case, it is not null, it just does not exist, and #Error is returned for any math operation concerning the missing value .
So wrapping it in IsError, you can force it to return Zero
    =IIF(IsError([Something]),0,[Something])

Sample attached, see rptEmployees

JimD also has another technique using a Function:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26786795.html

JeffCoachman
Access-EEQ-27974958SubFormSubRep.mdb
0
 

Author Comment

by:the_Apple
ID: 38737579
Hi all,

=NZ([Temp_Radiology subreport].[Report]![GrandTotal],0)+NZ([TBL_PRICER_JHH subreport].[Report]![AccessTotalsAMT],0)

it worked only in a layout view, when I run it to report preview or print. it shows error.

Any help or suggestions.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 38737691
<layout view>
Don't you mean "Report View?
This is why I don't like the way the new "Views" are dealt with in the newer versions of Access.
When you first create a "Quick" report in Access, it opens in "Layout View" (the same thing happens with forms)
But clicking on the View button will bring you to "Report View"
This is why I never use Report View.
AFAIC, the only advantage Report View offers is; some limited "Interaction" (filtering, clicking on controls, ...etc) , and moderately lower system resource use.

I only use Print Preview because it will give you the best representation of exactly what the report will look like when actually "printed" (Hardcopy)

In this case IsError(), or Jim's function should work in any View.

The ultimate issue here is that if a sub-report has no value in may not be "Null", ...it just "does not exist"

JeffCoachman
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question