Re: Multiple Search Criteria with AND/OR???

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"Elvis72" <Elvis72@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:42B9F746-182F-472D-8BC8-750701D32FF7@xxxxxxxxxxxxxxxx
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" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"Elvis72" <Elvis72@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:022EE5D3-DDAB-479C-9E7C-910887EA4DBD@xxxxxxxxxxxxxxxx
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







.

Relevant Pages

  • Re: Query from field w/multiple values
    ... > What I mean is can I enter multiple criteria in the same criteria request? ... > For example I have a query that asks for a plant code. ... > to enter any number of plant codes they need in the same criteria field. ...
    (microsoft.public.access.queries)
  • Re: OKay, BUt........................
    ... Doug Steele, Microsoft Access MVP ... (no private e-mails, please) ... The text box is [plant ID], ...
    (microsoft.public.access.formscoding)
  • Re: Help needed with Date Add function
    ... SELECT Plant,, Scale ... you need to add a calculated field ... Criteria: < DateSerial),Month)+1,0) ...
    (microsoft.public.access.queries)
  • Silver birch or like recommendation please!
    ... We want to plant a couple of feature trees, like silver birch. ... The criteria are: ...
    (uk.rec.gardening)
  • Re: Plant Finder Software?
    ... Now if someone created an online plant database searchable by different ... criteria as the original poster suggested, ... Now there's a business idea for someone. ... What is needed is a hybrid between a businessperson, gardener and software ...
    (uk.rec.gardening)