Hi.
I have tried to go around this by trying to convert nulls into 0s but I
don't think I'm giong about this the right way.
If there are no records to display, then the #Error in the text box can be
circumvented by using a little bit of code. In the report's OnNoData( )
event, a message box can be displayed alerting the user that there is no data
and the event cancelled. For example,
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrHandler
MsgBox "Sorry. There's no data to display."
Cancel = True
Exit Sub
ErrHandler:
MsgBox "Error in Report_NoData( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
If you have code in a form that opens the report, then the cancelling of the
report will generate a run-time error which can be handled in the form
procedure's error handler. For example:
Public Sub OpenRpt(sRptName As String)
On Error GoTo ErrHandler
DoCmd.OpenReport sRptName, acViewPreview
Exit Sub
ErrHandler:
If (Err.Number <> 2501) Then ' Report not cancelled.
MsgBox "Error in OpenRpts( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
End If
Err.Clear
End Sub
If there are records to be displayed on the report and the opening of the
report doesn't need to be cancelled, then your text box's Control Source can
use the following syntax:
=Nz(Count([WorkOrderID]),0)
.... where WorkOrderID is the name of the field where the records need to be
counted. In the case where there are no records meeting the report's record
source criteria, 0 will be displayed.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
