How to Search Multiple Values in a Filter

[Melissa]

I am using the search criteria form created by Allen Browne.

Is there a way to enter multiple values in one search filter? For example,
State is a search value. I'd like the filter to return results for CA and CO.
As it is today, I can only search CA or CO.

Here is the code I am using to run the filter, compliments of Allen Browne.

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] = """ & Me.txtFilterRegion & """)
AND "
End If

If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ & Me.txtFilterState &
""") AND "
End If


If Not IsNull(Me.TxtFilterCategory) Then
strWhere = strWhere & "([Category] = """ & Me.TxtFilterCategory &
""") AND "
End If


If Not IsNull(Me.txtFilterShipTo) Then
strWhere = strWhere & "([Ship To] Like ""*" & Me.txtFilterShipTo &
"*"") AND "
End If

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



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

 

[Allen Browne]

You will need to write some code to handle this.

Use Split() to parse the words in txtFilterState.
Build a string that uses the IN operator.
The target string will look like this:
"([MarketState] IN (""CA"", ""CO"", ""TX"")"

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

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

I am using the search criteria form created by Allen Browne.

Is there a way to enter multiple values in one search filter? For example,
State is a search value. I'd like the filter to return results for CA and
CO.
As it is today, I can only search CA or CO.

Here is the code I am using to run the filter, compliments of Allen
Browne.

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] = """ & Me.txtFilterRegion & """)
AND "
End If

If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ & Me.txtFilterState &
""") AND "
End If

[snip]

 

[Melissa]

Thanks for responding so quickly Allen.

Do I have to enter every possible value in the code for this to work? The
reason I ask is I also have a customer number filter. There are over a
thousand customer numbers and they change frequently. For example, I have
customer 123456 and customer 11111. I need to be able to enter both values in
the customer number filter. Is that possible?

Thanks,
Melissa

You will need to write some code to handle this.

Use Split() to parse the words in txtFilterState.
Build a string that uses the IN operator.
The target string will look like this:
"([MarketState] IN (""CA"", ""CO"", ""TX"")"

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

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

I am using the search criteria form created by Allen Browne.

Is there a way to enter multiple values in one search filter? For example,
State is a search value. I'd like the filter to return results for CA and
CO.
As it is today, I can only search CA or CO.

Here is the code I am using to run the filter, compliments of Allen
Browne.

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] = """ & Me.txtFilterRegion & """)
AND "
End If

If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ & Me.txtFilterState &
""") AND "
End If

[snip]

 

[Allen Browne]

The suggestion was to use code to parse the words.

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

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

Thanks for responding so quickly Allen.

Do I have to enter every possible value in the code for this to work? The
reason I ask is I also have a customer number filter. There are over a
thousand customer numbers and they change frequently. For example, I have
customer 123456 and customer 11111. I need to be able to enter both values
in
the customer number filter. Is that possible?

Thanks,
Melissa

You will need to write some code to handle this.

Use Split() to parse the words in txtFilterState.
Build a string that uses the IN operator.
The target string will look like this:
"([MarketState] IN (""CA"", ""CO"", ""TX"")"

I am using the search criteria form created by Allen Browne.

Is there a way to enter multiple values in one search filter? For
example,
State is a search value. I'd like the filter to return results for CA
and
CO.
As it is today, I can only search CA or CO.

Here is the code I am using to run the filter, compliments of Allen
Browne.

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] = """ & Me.txtFilterRegion &
""")
AND "
End If

If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ & Me.txtFilterState
&
""") AND "
End If

[snip]

 

[Melissa]

Thank you Allen. VBA code is not something I am familiar with. I appreciate
your help.

--
Melissa

The suggestion was to use code to parse the words.

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

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

Thanks for responding so quickly Allen.

Do I have to enter every possible value in the code for this to work? The
reason I ask is I also have a customer number filter. There are over a
thousand customer numbers and they change frequently. For example, I have
customer 123456 and customer 11111. I need to be able to enter both values
in
the customer number filter. Is that possible?

Thanks,
Melissa

You will need to write some code to handle this.

Use Split() to parse the words in txtFilterState.
Build a string that uses the IN operator.
The target string will look like this:
"([MarketState] IN (""CA"", ""CO"", ""TX"")"

I am using the search criteria form created by Allen Browne.

Is there a way to enter multiple values in one search filter? For
example,
State is a search value. I'd like the filter to return results for CA
and
CO.
As it is today, I can only search CA or CO.

Here is the code I am using to run the filter, compliments of Allen
Browne.

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] = """ & Me.txtFilterRegion &
""")
AND "
End If

If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ & Me.txtFilterState
&
""") AND "
End If
[snip]

 

[Melissa]

Hi Allen,

Just to give you a little background, I have no experience with VBA. The
tutorial you posted on building a search criteria form was so user-friendly
and easy to understand that I was able to figure out how to build the form
off of it. Any modification of the form becomes tricky for me. So, I
apologize if the responses you have given to me are unclear for me.

--
Melissa

Thank you Allen. VBA code is not something I am familiar with. I appreciate
your help.

--
Melissa

The suggestion was to use code to parse the words.

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

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

Thanks for responding so quickly Allen.

Do I have to enter every possible value in the code for this to work? The
reason I ask is I also have a customer number filter. There are over a
thousand customer numbers and they change frequently. For example, I have
customer 123456 and customer 11111. I need to be able to enter both values
in
the customer number filter. Is that possible?

Thanks,
Melissa


:

You will need to write some code to handle this.

Use Split() to parse the words in txtFilterState.
Build a string that uses the IN operator.
The target string will look like this:
"([MarketState] IN (""CA"", ""CO"", ""TX"")"

I am using the search criteria form created by Allen Browne.

Is there a way to enter multiple values in one search filter? For
example,
State is a search value. I'd like the filter to return results for CA
and
CO.
As it is today, I can only search CA or CO.

Here is the code I am using to run the filter, compliments of Allen
Browne.

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] = """ & Me.txtFilterRegion &
""")
AND "
End If

If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ & Me.txtFilterState
&
""") AND "
End If
[snip]