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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
VibertHConnect With a Mentor Author 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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this first:

=Nz(Sum(Nz([inv_amt])))
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
If that doesn't work, try this - I'm using generic names:

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

mx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
All Courses

From novice to tech pro — start learning today.