Access 2007Nz and nvl functions don't work on report field with a Sum if no records are present

try this:

=IIF(DCount("*","[YourReportsQueryOrTableName"])=0, "no records today", <YourNormalFunctionHere>)

mx

Paste the attached code in a general module.  Then wrap your reference to the subform control with:
=AvoidError(<Control reference here>,0)
JimD.
 

Function AvoidError(n As Variant, varReplaceWith As Variant)
         
    On Error GoTo AvoidError_Error
    
    AvoidError = Nz(n, varReplaceWith)
    
AvoidError_Exit:
    Exit Function
 
AvoidError_Error:
    AvoidError = varReplaceWith
    Resume AvoidError_Exit
    
End Function

Select allOpen in new window

Unfortunately, when there are no records ... none of the other tricks work ... Nz(), testing for Null, etc.

mx

use the hasdata property of the subreport to hide the subreport when there is no record to show

Returning zero ... sorry:

=IIF(DCount("*","[YourReportsQueryOrTableName"])=0, 0 , <YourNormalFunctionHere>)

mx

The problem is I have 2 sub reports on the same report.  The subreports have separate queries which populate each subreport.  On the main report, I need to total the two Sum fields of the subreports.  But, if either subreport contains no data, then the whole thing breaks.

The first calc on Subreport A that needs to happen is:  =sum([column1] or =Nz(Sum([column1]),0) or something that forces that sum field to be 0.

Then Subreport B needs to do the same thing:  =sum([column2] or =Nz(Sum([column2]),0) or something

Then the main report needs to add the 2 results together:  =([Column1]+[Column2])

But, if anything is Null due to no records in the underlying query, i get #error in the field and can't continue on.

JimD, I tried your custom function but still have blank field.  Attached is Image of the results, and also what I am trying to accomplish.  This is the form view, so they are 2 subforms on a main form.  The main form has the calculations at the bottom that reference the subform, where the record sources are and I need the zero values.
Nz.JPG

See if this works:  put this in a text box on your main report

=IIf(IsError([SubReportA].[Report]![Column1]),0,[SubReportA].[Report]![Column1])+IIf(IsError([SubReportB].[Report]![Column2]),0,[SubReportB].[Report]![Column2])

No luck, the IsError function does not seem to detect the "#error" state.  I tried both on the primary sum field, and the main report "Subtotal" field that actually contains #error, but it still does the same thing.

"#error" is kind of a virtual display ... doesn't really exist in the text box per se.

mx

Instead of putting the Nz function in your subreport sum fields use it instead in the subtotal box on your main report,

eg

= nz([SubReportA].[Report]![Column1],0) + nz([SubReportB].[Report]![Column2])

Leigh

Ok, Leigh had a good idea but it still resulted in a blank field.  Didn't work.  The HasData idea had the most merit, but it appears that it works more specifically for Reports rather than forms.

However, this definitely led to a fix that I came up with.  I made an OnLoad event for each subform and put in the following code:

If Me.Recordset.RecordCount < 1 Then

Me.SumLaborCharges.Value = 0

Else

Me.SumLaborCharges.ControlSource = "=Sum([ExtendedPrice])"

End If


This would test to see if there are no records on the particular subform, and then just make the control value 0.  If there are records, it sets the controlsource to the original function I needed, which is a sum of the field "ExtendedPrice" within the recordset.

Once applied to both subforms, the fields on the main form totaled perfectly because there were real numbers in the subform controls.

netsmithcentral:
Did you try my post @ http:#a32865104 ?

mx

And if anyone is interested, here's the thread I refered to with the testing:
https://www.experts-exchange.com/questions/24272099/Replace-Error-with-0-in-Query.html
JimD.

"The HasData idea [...] works more specifically for Reports rather than forms."

Yes. The title, body, and comments all speak about reports and sub-reports. I don't think you mentioned forms at all before... Anyway, the equivalent for forms is indeed

  =IIf(subDetails.Form.Recordset.RecordCount,subDetails!txtSumAmt,0)

Note however that if you leave "AllowAdditions: Yes", then the sum will not be an error, but just Null, so that

  =Nz(subDetails!txtSumAmt)

works as expected.
Cheers!
(°v°)
_____
JimD:

Interesting discussion! I would have added my 2 cents -- (^v°)

RE:

"=IIf(subDetails.Form.Recordset.RecordCount,subDetails!txtSumAmt,0)"

Not positive ... but seems I tried using this syntax (except RecordsetClone) in A2007 and it no longer works ... ?

RE:
"IsError seems to be broken,"
Not sure it's broken, I just don't think it 'sees' #Error ...

mx

I definitely used iserror as recently as 2003 for a nearly identical problem so it is very strange

> ... and it no longer works ... ?

That would be a bad surprise! Maybe it was some non-standard form, e.g. I never tried it on forms in pivot table view...

> IsError seems to be broken

IsError was first an Excel built-in function, passed to ExcelVBA, and recycled partially to handle error exceptions in forms. Note: forms, not queries. Access partially understand error codes returned by VB and uses them to avoid error *conditions* on forms. "=IsError(1/0)" works as expected in a control source, and a call to a non-existing "=Foobar()" shows #Name, not #Error...

Jet uses only error *conditions*. The query "SELECT IsError(1/0);" doesn't run. The occasional #Error or #Deleted message in table view is inserted by the Access GUI, not by Jet. Jet doesn't understand Objects or Variants. If a function returns a Variant with subtype Error, Jet throws and exception.

In the Spreadsheet data model, it makes sense to identify various types of errors and propagate them in order to create trace tools to pinpoint the origin. In a database, it doesn't make any sense, and Null is the only useful "error code", it propagates just like errors in Excel.

As said by others before, the best strategy in Access is always to find the reason behind the error and resolve it at the lowest level possible.

(°v°)

> about the function AvoidError()

I don't see it working. On a query, "SELECT AvoidError(1/0,'ouch')" throws the exception. On a form, it shows #Div/0!, not 'ouch', from the immediate pane, you get the error message again. It was mean, because "division by zero" is actually a processor interrupt... Still, it's not very useful.

The reason is simple, in order to pass the parameter 'n' to the function, the expression evaluator needs to convert the error to a Variant. Jet doesn't know anything about Variants, and throws the exception instead.

I should stop here...

(°v°)

<<> about the function AvoidError()>>
  It basically onlys work when it's used with a control on a form and in terms of doing so, catches more errors then anything else.  I don't think you should allow the error in the first place, but that's another story.
JimD.

Hi everyone, I'm sure all of us are more a less correct in one way or another, depending on the version of access, which function can detect the Null value, whether or not HasData property can be used, etc. etc.  Thank you all for the help, it definitely led to my work around above of essentially "tricking" the control into either forcing a 0 value based on the recordset count, or setting the controlvalue property to my original "=sum([fieldname])" that is built in to access (and works properly when data does exist).

I did mislabel the question a bit as far as forms vs. reports, sorry about that.  I actually have run into the same problem in my database for both forms and reports, but in this case that screen shot was actually the form.  Later on after I process my data, I print it as a report and the same glitch occurs but now I know how to trick it into displaying a "0" when needed.

I've seen so many other posts on this problem that I think the extra comments and discussion is good for the forum, so thanks for that.  I am going to assign the main 2 contributors the points, I hope that is ok with everybody.  I didn't try any solutions beyond the work around that I came up with myself, so for the benefit of people searching the site, I am going to call that the answer.  Thanks again!

Here is the final fix I ended up using:

However, this definitely led to a fix that I came up with.  I made an OnLoad event for each subform and put in the following code:

If Me.Recordset.RecordCount < 1 Then

Me.SumLaborCharges.Value = 0

Else

Me.SumLaborCharges.ControlSource = "=Sum([ExtendedPrice])"

End If


This would test to see if there are no records on the particular subform, and then just make the control value 0.  If there are records, it sets the controlsource to the original function I needed, which is a sum of the field "ExtendedPrice" within the recordset.

Once applied to both subforms, the fields on the main form totaled perfectly because there were real numbers in the subform controls.

If Me.Recordset.RecordCount < 1 Then

Me.SumLaborCharges.Value = 0

Else

Me.SumLaborCharges.ControlSource = "=Sum([ExtendedPrice])"

End If

Select allOpen in new window

> I hope that is ok with everybody.

It is, don't worry about that. If you find a solution that works, derived from an Expert comment or from your own research, you simply assign points to the most relevant comments, and move on.

In any case, thank you and success with your project!
(°v°)