Report with calculated field returns #Error when query returns null.

[lwilly]

I have a report with three calculated fields. Report is based on a query of a table. How do I catch an error before it returns #Error to the text box? If my query returns no records I would like to put zero in the fields.<br>TIA

 

[WP]

Do the calc of the field in the recordset query.&nbsp;&nbsp;Use Iif function to test for null and if null the set column to 0.<br><br>e.g. CalcField: iif(isnull([field1]),0,[Field1] + [Field2])<br><br>Good luck,<br><br>WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[jgarnick]

From Help under Nz function:<br><br>You can use the Nz function to return zero, a zero-length string (&quot; &quot, or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.<br><br>Syntax<br><br>Nz(variant[, valueifnull]) <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[lwilly]

I have tried both suggestions and still no luck. Maybe it me. When I put IIf(IsNull(myfieldname), 0, myfieldname), I still return no records in my query. I think my error comes from doing a calculation on nothing. <br>Any more suggestions?<br>

 

[JimConrad]

Are the fields used by the calculations included in the report?<br><br>If not, include them (make them invisible if you don't need them to appear). <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[elizabeth]

There is an OnNoData event on a report that you can use to pop up a message box to the user and cancel the Report. Here's the Help file example:<br><br>Private Sub Report_NoData(Cancel As Integer)<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;The report has no data.&quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& chr(13) & &quot;Printing is canceled. &quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& chr(13) & &quot;Check the data source for the &quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& chr(13) & &quot;report. Make sure you entered &quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& chr(13) & &quot;the correct criteria (for &quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& chr(13) & &quot;example, a valid range of &quot; _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& chr(13) & &quot;dates),.&quot; vbOKOnly + vbInformation<br>&nbsp;&nbsp;&nbsp;&nbsp;Cancel = True<br>End Sub<br>

 

[lwilly]

Here is a little background on what I have going. I have a main report that consist of two subreports. One of the subreports has a calculated field &quot;=sum([IVC_TOT2])&quot;. When the query that this report is based on returns no records, this field displays &quot;#Error&quot;. This field is also used to calculate fields in my main report. For example: sum([IVC_TOT2]) is a total of all invoices for a customer within a given time period, and [IncrDecr]=mainreport![IVC_TOT1]-subreport1![IVC_TOT2]. <br>Any time it does a calculation where there is no value/record it gives me &quot;#Error&quot; If I cancel the subreport in the NoData event I still get the #Error in my main report.<br>When I use iif(isnull(IVC_TOT]),0,[IVC_TOT]) in my query I still does not return a row.