A
ATSBC03
I have created a form to filter my report. The report is based on a query and
the form is an unbound form with combo boxes and text fields to allow data to
be selected. Everything in the code below works as it is supposed to.
However, the report as textboxes comments pulled from my table. I want to be
able to filter those if the user decides they only want provider comments or
staff comments or general comments or no comments. The form has a combo box
(cboprovcomm) with a yes or no selection. The comments in the table are memo
format and my question is how do i use code in the event below to filter the
report if the user chooses yes to provider but no to the other two
selections. I would only want provider comments shown. I have been trying to
use Null and Not Null but that isn't working. Any Help would be appreciated.
Private Sub cmdOK_Click()
On Error GoTo ErrorHandler
Dim strDocName As String
Dim strWhere As String
Dim strOccDate As String
Dim strRptDate As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strWhere = "1=1"
stdocname = "rptAllComments"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End Date, no start Date
strWhere = strWhere & " AND ( [Date Occurred] <= " &
Format(Me.txtEndDate, conDateFormat) _
& " OR [Date Reported] <= " & Format(Me.txtEndDate,
conDateFormat) & " ) "
End If
Else
If IsNull(Me.txtEndDate) Then ' Start Date, no end date
strWhere = strWhere & " AND ( [Date Occurred] >= " &
Format(Me.txtStartDate, conDateFormat) _
& " OR [Date Reported] >= " & Format(Me.txtStartDate,
conDateFormat) & " ) "
Else 'Both start and End Date
strWhere = strWhere & " AND ( [Date Occurred] Between " &
Format(Me.txtStartDate, conDateFormat) _
& " AND " & Format(Me.txtEndDate, conDateFormat) _
& " OR [Date Reported] Between " & Format(Me.txtStartDate,
conDateFormat) _
& " AND " & Format(Me.txtEndDate, conDateFormat) & " ) "
End If
End If
If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND ( [Location] = """ & _
Me.cboSite & """ OR [Location2] = """ & _
Me.cboSite & """ OR [Location3] = """ & _
Me.cboSite & """ ) "
End If
If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND ( [Dept Involved] = """ & _
Me.cboDept & """ OR [Dept Involved2] = """ & _
Me.cboDept & """ OR [Dept Involved3] = """ & _
Me.cboDept & """ ) "
End If
If Me.cboProvComm = "Yes" Then
strWhere = strWhere & " AND [ProviderComment] = NULL"
End If
Debug.Print strWhere
DoCmd.OpenReport stdocname, acViewPreview, , strWhere
Me.Visible = False
Exit Sub
ErrorHandler:
If Err.Number = 2501 Then Resume Next
End Sub
the form is an unbound form with combo boxes and text fields to allow data to
be selected. Everything in the code below works as it is supposed to.
However, the report as textboxes comments pulled from my table. I want to be
able to filter those if the user decides they only want provider comments or
staff comments or general comments or no comments. The form has a combo box
(cboprovcomm) with a yes or no selection. The comments in the table are memo
format and my question is how do i use code in the event below to filter the
report if the user chooses yes to provider but no to the other two
selections. I would only want provider comments shown. I have been trying to
use Null and Not Null but that isn't working. Any Help would be appreciated.
Private Sub cmdOK_Click()
On Error GoTo ErrorHandler
Dim strDocName As String
Dim strWhere As String
Dim strOccDate As String
Dim strRptDate As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strWhere = "1=1"
stdocname = "rptAllComments"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End Date, no start Date
strWhere = strWhere & " AND ( [Date Occurred] <= " &
Format(Me.txtEndDate, conDateFormat) _
& " OR [Date Reported] <= " & Format(Me.txtEndDate,
conDateFormat) & " ) "
End If
Else
If IsNull(Me.txtEndDate) Then ' Start Date, no end date
strWhere = strWhere & " AND ( [Date Occurred] >= " &
Format(Me.txtStartDate, conDateFormat) _
& " OR [Date Reported] >= " & Format(Me.txtStartDate,
conDateFormat) & " ) "
Else 'Both start and End Date
strWhere = strWhere & " AND ( [Date Occurred] Between " &
Format(Me.txtStartDate, conDateFormat) _
& " AND " & Format(Me.txtEndDate, conDateFormat) _
& " OR [Date Reported] Between " & Format(Me.txtStartDate,
conDateFormat) _
& " AND " & Format(Me.txtEndDate, conDateFormat) & " ) "
End If
End If
If Not IsNull(Me.cboSite) Then
strWhere = strWhere & " AND ( [Location] = """ & _
Me.cboSite & """ OR [Location2] = """ & _
Me.cboSite & """ OR [Location3] = """ & _
Me.cboSite & """ ) "
End If
If Not IsNull(Me.cboDept) Then
strWhere = strWhere & " AND ( [Dept Involved] = """ & _
Me.cboDept & """ OR [Dept Involved2] = """ & _
Me.cboDept & """ OR [Dept Involved3] = """ & _
Me.cboDept & """ ) "
End If
If Me.cboProvComm = "Yes" Then
strWhere = strWhere & " AND [ProviderComment] = NULL"
End If
Debug.Print strWhere
DoCmd.OpenReport stdocname, acViewPreview, , strWhere
Me.Visible = False
Exit Sub
ErrorHandler:
If Err.Number = 2501 Then Resume Next
End Sub