Solved

subform value

Posted on 2012-12-20
4
187 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now