Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

subform value

Posted on 2012-12-20
4
Medium Priority
?
253 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 2000 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 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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 …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

916 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