Filter a Report with Several Criteria

  • Thread starter Thread starter If
  • Start date Start date
I

If

Hello,

I have the code below which does not work as I would like it.
I would like that the sorting can be done on the "operator" and also over
the periods (dates)
For the moment, it's only search on the operator or search on the dates, but
the two criteria are not taken together.
I wish filter my report on the name of Operator and one period (Date Range)

Thank you in advance for your assistance

Yves


-------------------------------------------------------------------------------------------------
Private Sub btnOK_Click()

Dim strWhere As String
Dim strEtat As String
Dim strDate As String

Const conDateFormat = "\#mm\/dd\/yyyy\#"

strDate = "Date"
strReport = "rpt Report1"


' Opérator
If Not IsNull(Me.cmbOperator) Then
If strWhere <> "" Then strWhere = strWhere & " AND "
strWhere = strWhere & "([Operator] LIKE '*" & Me.cmbOperator & "*')"
End If

' Périod
If IsNull(Me.txtDateStart) Then
If Not IsNull(Me.txtDateStart) Then
strWhere = strDate & " < " & Format(Me.txtDateStart, conDateFormat)
End If
Else
If IsNull(Me.txtDateStart) Then
strWhere = strDate & " > " & Format(Me.txtDateStart, conDateFormat)
Else 'Both start and end dates.
strWhere = strDate & " Between " & Format(Me.txtDateStart,
conDateFormat) _
& " And " & Format(Me.txtDateEnd, conDateFormat)
End If
End If


DoCmd.Close
DoCmd.OpenReport strEtat, acViewPreview, , strWhere

End Sub
 
Switch the code around, so the date is handled first, and then the operator.

The code in the "Opirator" block tacks an " AND " on if needed.
 
Thanks

Allen Browne said:
Switch the code around, so the date is handled first, and then the
operator.

The code in the "Opirator" block tacks an " AND " on if needed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If said:
Hello,

I have the code below which does not work as I would like it.
I would like that the sorting can be done on the "operator" and also over
the periods (dates)
For the moment, it's only search on the operator or search on the dates,
but the two criteria are not taken together.
I wish filter my report on the name of Operator and one period (Date
Range)

Thank you in advance for your assistance

Yves


-------------------------------------------------------------------------------------------------
Private Sub btnOK_Click()

Dim strWhere As String
Dim strEtat As String
Dim strDate As String

Const conDateFormat = "\#mm\/dd\/yyyy\#"

strDate = "Date"
strReport = "rpt Report1"


' Opirator
If Not IsNull(Me.cmbOperator) Then
If strWhere <> "" Then strWhere = strWhere & " AND "
strWhere = strWhere & "([Operator] LIKE '*" & Me.cmbOperator & "*')"
End If

' Piriod
If IsNull(Me.txtDateStart) Then
If Not IsNull(Me.txtDateStart) Then
strWhere = strDate & " < " & Format(Me.txtDateStart,
conDateFormat)
End If
Else
If IsNull(Me.txtDateStart) Then
strWhere = strDate & " > " & Format(Me.txtDateStart,
conDateFormat)
Else 'Both start and end dates.
strWhere = strDate & " Between " & Format(Me.txtDateStart,
conDateFormat) _
& " And " & Format(Me.txtDateEnd, conDateFormat)
End If
End If


DoCmd.Close
DoCmd.OpenReport strEtat, acViewPreview, , strWhere

End Sub
Click to expand...
Click to expand...
 
Back
Top