Multiple Filter Search Form - AND/OR?? 2

[Elvis72]

Same Search Form as the previous post but it just hit me that either you have all the criteria given or you have nothing.

So how would I change this to an AND/OR Search?

Private Sub Command12_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.txtfirstname) Then
strWhere = strWhere & "([First Name] Like ""*" & Me.txtfirstname & "*"") AND "
End If


If Not IsNull(Me.txtlastname) Then
strWhere = strWhere & "([Last Name] Like ""*" & Me.txtlastname & "*"") AND "
End If

If Not IsNull(Me.txtexperience) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience & "*"") AND "
End If

If Not IsNull(Me.txtexperience1) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience1 & "*"") AND "
End If
If Not IsNull(Me.txtexperience2) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience2 & "*"") AND "
End If
If Not IsNull(Me.txtexperience3) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience3 & "*"") AND "
End If

If Me.ckplantservices = -1 Then
strWhere = strWhere & "([Plant Services] = True) AND "
ElseIf Me.ckplantservices = 0 Then
strWhere = strWhere & "([Plant Services] = False) AND "
End If

If Not IsNull(Me.txtyrsexp) Then
strWhere = strWhere & "([Years Experience]>='" & Me.txtyrsexp & "') AND "
End If
If Not IsNull(Me.txtyrsexpend) Then
strWhere = strWhere & "([Years Experience]<='" & Me.txtyrsexpend & "') AND "
End If

If Not IsNull(Me.txtplanttype) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype & "*"") AND "
End If

If Not IsNull(Me.txtplanttype1) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype1 & "*"") AND "
End If

If Not IsNull(Me.txtplanttype2) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype2 & "*"") AND "
End If

If Not IsNull(Me.txtplanttype3) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype3 & "*"") AND "
End If

If Not IsNull(Me.memoresume) Then
strWhere = strWhere & "([Resume Memo] Like ""*" & Me.memoresume & "*"") AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[Elvis72]

OK...this does resolve the getting the AND back in the combobox, and it does not refresh the filter.

But now when I type in an AND filter it filters great...then I clear it and do another AND filter and its great.

If I do a OR filter it works great...if I clear the filter and do another OR filter it does not work...

If I do an AND filter it works great...if I clear the filter and do an OR filter it does not work...

 

[MajP]

Can you provide your final command19 code for clearing the form? I can not recreate the problem.

 

[Elvis72]

Private Sub Command19_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox

If Not ctl.Name = "Combo53" Then
ctl.Value = Null
End If

Case acCheckBox
ctl.Value = False
End Select
Next
Me.Refresh
'Me.Combo53.Value = "AND"

'Remove the form's filter.[
Me.FilterOn = False

End Sub

 

[PHV]

I'd replace this:
ctl.Value = False
with this:
ctl.Value = Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Elvis72]

That did the trick!~

Thanks sooo much!~