Multiple search criteria (combo, list & text )?

[Guest]

Hi friends,
I have been searching the discussions and the web and found one of Allen
Browne's posting that was close to what I was trying to get at but I still
get nothing filtered onto the report.

Scenario: I have a report bound to a query and have multiple filter criteria
(ie including combos, lists, and textboxes all sitting on different tabs on
an unbound form (all unbound too), with a Button (onclick) that builds WHERE
string
and then apply it to the form's Filter or the report's
WhereCondition. I do that with the code below but I am not gettin anything
filtered on the report even I know there are some records that meet the
criteria...
What am I doing wrong? Can anyone help me?

Private Sub btnPreview_Click()
'On Error GoTo Err_btnPreview_Click

Dim cbo As ComboBox
Dim lst As ListBox
Dim strWhere As String
Dim iLen As Integer

'CropName Combo
Set txtCbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo & """) AND "
End If

'GrowerName List
Set txtLst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst & """) AND "
End If

'Similar for other list, combo or text boxes here.

'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, iLen)

'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True

'Open the report and apply filter.
DoCmd.OpenReport "rptSowing", acViewPreview, , strWhere
End If

Set cbo = Nothing
Set lst = Nothing
End Sub

 

[Guest]

I have managed to get it working after careful tiral and error methods
but now I am stuck on one thing.
Again, my report is bound to a query and with the code below I supply all
the necessary WHERE CLAUSE but I want it to return everything onto the report
if none of the criteria is selected or provided. Im suspecting I should add
something onto the part of the code below where it returns the MsgBox "No
Criteria" IF iLen < 1
Please help.

Private Sub btnPreview_Click()

Dim cbo As ComboBox
Dim lst As ListBox
Dim strWhere As String
Dim iLen As Integer

'CropName Combo
Set cbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo.Column(1) & """) AND "
End If

'GrowerName List
Set lst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst.Column(1) & """) AND "
End If

'VarietyName List
Set lst = Me.lstVariety
If Not IsNull(lst) Then
strWhere = strWhere & "(VarietyName = """ & lst.Column(1) & """) AND "
End If


'Similar for other list, combo or text boxes here.

'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "No criteria"
Else
strWhere = Left$(strWhere, iLen)

'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True

'Open the report and apply filter.
DoCmd.OpenReport "rptSowing", acViewPreview, , strWhere
End If

Set cbo = Nothing
Set lst = Nothing

End Sub

 

[Guest]

oh one more question,
I have a date range to include in the search criteria as well and I have
this code below but how do I stick that into what I got already so I can use
the date range criteria?

Dim strDateField As String 'Name of your criteria date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptSowing"
strDateField = "DateSown"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strDateField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strDateField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strDateField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If