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

Try this first:

=Nz(Sum(Nz([inv_amt])))

If that doesn't work, try this - I'm using generic names:

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

mx

=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.  

"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

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?

"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

--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])) )));

Select allOpen in new window

totDue.doc

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

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).

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

"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.  

is there anyone out there that can help me

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.