Experts Exchange connects you with the people and services you need so you can get back to work.

subform value

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

the_Apple

Asked: 2012-12-20

Advertise Here

Microsoft Access

2 Solutions

LVL 29

IrogSintaCommented: 2012-12-20

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.

LVL 74

Jeffrey CoachmanMIS LiasonCommented: 2012-12-20

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

the_AppleAuthor Commented: 2013-01-02

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.

LVL 74

Jeffrey CoachmanMIS LiasonCommented: 2013-01-02

<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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

subform value

Join & Write a Comment Already a member? Login.

Featured Post