Subreport running total is blank (control source is =Sum([inv_amt]))

I have a subreport that totals up values displayed in the details.  The sum is displayed in the report footer (the control source is '=Sum([inv_amt])').  If there are no records that meet the criteria, the sum should be 0.  Instead it prints #Error.  On the main report it is blank.  How do I get this to print 0?  
VibertHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try this first:

=Nz(Sum(Nz([inv_amt])))
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If that doesn't work, try this - I'm using generic names:

=IIF( Nz( DCount("*","[YourSubReporQueryOrTableName]") ,0 ) = 0 , Null, Sum([inv_am]) )

mx
0
VibertHAuthor Commented:
=Nz(Sum(Nz([inv_amt])))

same error and total due is blank on the report

=IIF( Nz( DCount("*","[YourSubReporQueryOrTableName]") ,0 ) = 0 , Null, Sum([inv_amt]) )

returns nothing but total due is blank on the form.  

Let me be more descriptive.  There are two sections that will be added together for a total due.  I am assuming that because one of the sections (subreports) returns either error or nothing, it cause the total due to be blank.  
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"two sections"
??

Where is the text box that is displaying the total due?  Main report? Sub Report ?

"same error and total due is blank on the report"

Sorry .... I'm confused.  Where does the error show up?  Where is the 'blank' ?

mx
0
VibertHAuthor Commented:
Sorry.  When I run the subreport alone,
=Nz(Sum(Nz([inv_amt])))

same error

=IIF( Nz( DCount("*","[YourSubReporQueryOrTableName]") ,0 ) = 0 , Null, Sum([inv_amt]) )

returns nothing

when I run the main report, the total is blank.  

do you still have the db I uploaded for the white space issue?  it's that same section.  if there are no oustanding balances, it should return 0 and then total due should be the first subreport (total time and disbursements) + the second subreport (total outstanding bal).  Does this make sense?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"returns nothing "

Now ... just to be sure, you need to replace 'YourSubReporQueryOrTableName'  with the actual name of the query or table name that is the recordsource of your subreport.  Did you do that?  What is the name of the table or query?

Did you try this

=IIF( Nz( DCount("*","[YourSubReporQueryOrTableName]") ,0 ) = 0 , Null, Sum([inv_amt]) )

on the subreport ?

Try this mod and see if it returns "no records":

=IIF( Nz( DCount("*","[YourSubReporQueryOrTableName]") ,0 ) = 0 , "no records", Sum([inv_amt]) )

I don't still have the mdb, sorry.

If you did that ... and you get nothing, it's because the recordsource has no records
0
VibertHAuthor Commented:
--If you did that ... and you get nothing, it's because the recordsource has no records

It doesn't have any.  I know that.  It's what I am testing.  I am looking at the query that populates the total due now text.  Here is my select statement for the total due text (TotDue).  

It basically takes [totDisbursement]+[totRate]+[totOutstanding] (code set 2+Code set 3+ code set 4)

Code set 4 returns nothing.  It's blank, and there are no records to select.  I want it to return 0, so what do I need to do so that the totDue can calculate properly.  

I've added a screen print of the code set 1 query.  

--code set 1
SELECT [totDisbursement]+[totRate]+[totOutstanding] AS TotDue, [Report-Total Due-Total Outstanding Balance].totOutstanding, [Report-Total Disbursement].totDisbursement, [Report-Total Time].TotRate
FROM [Report-Total Disbursement], [Report-Total Time], [Report-Total Due-Total Outstanding Balance];
 
--code set 2 
SELECT Sum(IIf([Forms]![Print_Invoice]![tbCurrency],[Forms]![Print_Invoice]![tbCurrency]*[FeeDisbursement.FeeDisbursement_Amount],[FeeDisbursement.FeeDisbursement_Amount])) AS totDisbursement
FROM Invoice INNER JOIN FeeDisbursement ON Invoice.Invoice_No = FeeDisbursement.Invoice_No
WHERE (((FeeDisbursement.Invoice_No)=[Forms]![Print_Invoice]![InvoiceNumber]));
 
--code set 3
SELECT Sum(IIf([Forms]![Print_Invoice]![tbCurrency],[Forms]![Print_Invoice]![tbCurrency]*(([TimeBillingExcel.TimeBillings_Time]*[ServiceProvider.serviceprovider_hourlyrate])),(([TimeBillingExcel.TimeBillings_Time]*[ServiceProvider.serviceprovider_hourlyrate])))) AS TotRate
FROM ServiceProvider INNER JOIN ((Matter INNER JOIN Invoice ON Matter.Matter_SubAcc = Invoice.Matter_SubAcc) INNER JOIN TimeBillingExcel ON Matter.Matter_SubAcc = TimeBillingExcel.Matter_SubAcct) ON ServiceProvider.ServiceProvider_Initials = TimeBillingExcel.ServiceProvider_Initials
WHERE (((Invoice.Invoice_No)=[Forms]![Print_Invoice]![InvoiceNumber]) AND ((TimeBillingExcel.Matter_SubAcct)=[Invoice].[Matter_SubAcc]) AND ((TimeBillingExcel.TimeBillings_Date) Between [invoice].[invoice_FromDate] And [invoice].[invoice_todate]));
 
 
--code set 4
SELECT (IIf([Forms]![Print_Invoice]![tbCurrency],[Forms]![Print_Invoice]![tbCurrency]*(Sum([Invoice.Invoice_Amount])),(Sum([Invoice.Invoice_Amount])))) AS totOutstanding
FROM Client INNER JOIN (Matter INNER JOIN Invoice ON Matter.Matter_SubAcc = Invoice.Matter_SubAcc) ON Client.Client_ID = Matter.Client_ID
WHERE (((Invoice.Invoice_Pd)=False) AND ((Client.Client_ID)=(SELECT Client.Client_ID
FROM (Client INNER JOIN Matter ON Client.Client_ID = Matter.Client_ID) INNER JOIN Invoice ON Matter.Matter_SubAcc = Invoice.Matter_SubAcc
WHERE (((Invoice.Invoice_No)=[Forms]![Print_Invoice]![InvoiceNumber])) )));

Open in new window

totDue.doc
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok.
Basically, you are trying to get rid of the #Error, right ?
Where is the text box (main or sub report) when you are getting # error.

mx
0
VibertHAuthor Commented:
Yes.  But I need for it to return a value so that the totdue query can calculate properly.  Sub report.  On the main report it not there which was why I was trying to get rid of the whitespace (there is still a small amount though).
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry ... but "But I need for it to return a value"

You need what to return a value ?  A query or the text box - return zero ?

"totdue query "

Which query is that ?

mx
0
VibertHAuthor Commented:
"totdue query "  Which query is that ?

code set 1

I need the query to return zero (code set 4) to return 0.  It is needed for totDue (code set 1) for calculation purposes.  
0
VibertHAuthor Commented:
is there anyone out there that can help me
0
VibertHAuthor Commented:
Will someone please tell me how to force a query to return 0 if no records are returned.  The query sums values from other queries.  If one of the other queries returns no records, then the total can't be calculated.  
0
VibertHAuthor Commented:
I got it.  nz(field,0)
SELECT (nz([totDisbursement],0))+(nz([totRate],0))+(nz([totOutstanding],0))+(nz([totLess],0)) AS TotDue, [Report-Total Due-Total Outstanding Balance].totOutstanding, [Report-Total Disbursement].totDisbursement, [Report-Total Time].TotRate, [Report-Total Due-Total Less Payments].totLess
FROM [Report-Total Disbursement], [Report-Total Time], [Report-Total Due-Total Outstanding Balance], [Report-Total Due-Total Less Payments];

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.