#Error in report totals

[Waynest]

I've designed a report which is working well except that it shows #Error in its group totals when there are no records to show. I've tried using IIF as follows but that hasnt had any effect...

=IIf(Sum([opqty])>0,PrsToDozPrs(Sum([OpQty])),"0&quot

Any tricks to avoiding this? I've thought of checking for a result before opening the report but it seems wasteful to run the query twice like this (its access 2000 with sql server)

 

[ghubbell]

I might suggest you try the Nz (Null to zero) function on your OpQty field first, and abandon the IIF. Gord
ghubbell@total.net

 

[Waynest]

That hasnt had any effect either, although I'm sure that will come in useful elsewhere. Any more suggesitons?

 

[ghubbell]

=IIf(Sum([opqty])>0,PrsToDozPrs(Sum([OpQty])),0). (Removed quotes...)
Verify your field's Name is opqty and if your Function PrsToDozPrs is ok. We'll get it...
Gord
ghubbell@total.net

 

[Waynest]

Already tried the zero without the quotes, no effect. The field names must be correct because everything is fine when there's at least one record retrieved by the query. Here's my function which works ok everywhere else

Public Function PrsToDozPrs(Prs As Variant) As Variant

On Error GoTo Error_Handler
Dim OutDozs As Single
Dim OutPrs As Integer

If Not IsNumeric(Prs) Then
GoTo Error_Handler
End If

OutDozs = Prs / 12
OutDozs = Int(OutDozs)
OutPrs = Prs - (OutDozs * 12)
PrsToDozPrs = Format(OutDozs, "###,###,##0&quot & "." & Format(OutPrs, "00&quot
Exit Function

Error_Handler:

OutDozs = 0
PrsToDozPrs = Format(OutDozs, "###,###,##0&quot & "." & Format(OutPrs, "00&quot

End Function


It seems to be specific to the 2 grouping totals - the detail line shows 0.00 as desired and so does the grand total in the report footer. Seems petty, but I just know the guy who's going to use the report is going to moan...