Not Nz

[David b]

Not Nz ?

I have a report with 2 sub reports on it. I am grabbing 2 totals from the sub
reports for the main report footer. If there is data in each sub report no
problem. However if one contains nothing I get error. I have been trying to use
Nz to force a zero without success.
This is what I have in the control source of a text box on the main report

=Nz([invoicesubsundriesrpt].[Report]![totsuns])

Any thoughts ?
David B

 

[dcx693]

Try =Nz([invoicesubsundriesrpt].[Report]![totsuns],0)

 

[David b]

Thanks for the reply. No that doesn`t do the trick
David b

 

[pbaldy]

Couple of ways here:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=57546

 

[Oldsoftboss]

Try:

=IIf(IsNumeric([invoicesubsundriesrpt].[Report]![totsuns]),[invoicesubsundriesrpt].[Report]![totsuns],0)

I have never come across "IsNumeric" in the help files, but picked it up here. (Rich I think)

HTH
Dave

 

[Rich]

Or even the HasData method

 

[David b]

using Hasdata method this is doing what I want.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim total as Currency

If Me![invoicesubsundriesrpt].[Report].HasData then
total = Me![invoicesubsundriesrpt].[Report]![totsuns]
End If
Me![txtTotal] = total
End Sub

Using the HasData test avoids an error if the [totsuns] text box does not
exist.

David b

 

[Rich]

You don't need vba to do this, an unbound textbox will give the same result

 

[David b]

Didn`t know that, Suits me fine. The simpler the better is my aim <bg>
david b