Multiple Search Criteria with AND/OR???

  • Thread starter Thread starter Elvis72
  • Start date Start date
E

Elvis72

I have this search form, which I need to make it look for each criteria and
the next...instead of just one of them.

I'm not sure how to do this?

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
 
I'm not sure I follow what you mean.

The code you've shown will use multiple criteria in the search, not just a
single criterion.
 
My problem is if I search for:

Experience1 - Proposals
Experience2 - Safety

The only thing it returns are the ones that have proposals AND safety...

I need one that will show everyone who has proposals and/or safety...



Douglas J. Steele said:
I'm not sure I follow what you mean.

The code you've shown will use multiple criteria in the search, not just a
single criterion.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Elvis72 said:
I have this search form, which I need to make it look for each criteria and
the next...instead of just one of them.

I'm not sure how to do this?

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
Click to expand...
Click to expand...
 
Unless the field Experience in your table is a list of multiple values, you
won't have a row that has

([Experience] Like "*Proposals*") AND ([Experience] Like "*Safety*")

Unfortunately, even changing the ANDs to ORs won't be sufficient:

([Experience] Like "*Proposals*") AND ([Experience] Like "*Safety*")

will pass rows having only one of the two phrases.

You'll need to also include a GROUP BY and a HAVING:

SELECT Field1, Field2, Field3
FROM MyTable
WHERE ([Experience] Like "*Proposals*") AND ([Experience] Like "*Safety*")
GROUP BY Field1, Field2, Field3
HAVING Count(*) = 2

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Elvis72 said:
My problem is if I search for:

Experience1 - Proposals
Experience2 - Safety

The only thing it returns are the ones that have proposals AND safety...

I need one that will show everyone who has proposals and/or safety...



Douglas J. Steele said:
I'm not sure I follow what you mean.

The code you've shown will use multiple criteria in the search, not just
a
single criterion.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Elvis72 said:
I have this search form, which I need to make it look for each criteria
and
the next...instead of just one of them.

I'm not sure how to do this?

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
Click to expand...
Click to expand...
Click to expand...
 
I have the Form setup to show the following search boxes:

First Name
Last Name
Plant Type1
Plant Type2
Plant Type3
Plant Type4
Experience1
Experience2
Experience3
Experience4

All the above are text boxes ecept Experience which references a list of
predefined records to choose from.

So, what I am gathering there is no way to have the form allow an "and" or
"or" search criteria without having to specifically identify the criteria in
the events?

Douglas J. Steele said:
Unless the field Experience in your table is a list of multiple values, you
won't have a row that has

([Experience] Like "*Proposals*") AND ([Experience] Like "*Safety*")

Unfortunately, even changing the ANDs to ORs won't be sufficient:

([Experience] Like "*Proposals*") AND ([Experience] Like "*Safety*")

will pass rows having only one of the two phrases.

You'll need to also include a GROUP BY and a HAVING:

SELECT Field1, Field2, Field3
FROM MyTable
WHERE ([Experience] Like "*Proposals*") AND ([Experience] Like "*Safety*")
GROUP BY Field1, Field2, Field3
HAVING Count(*) = 2

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Elvis72 said:
My problem is if I search for:

Experience1 - Proposals
Experience2 - Safety

The only thing it returns are the ones that have proposals AND safety...

I need one that will show everyone who has proposals and/or safety...



Douglas J. Steele said:
I'm not sure I follow what you mean.

The code you've shown will use multiple criteria in the search, not just
a
single criterion.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have this search form, which I need to make it look for each criteria
and
the next...instead of just one of them.

I'm not sure how to do this?

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top