How to apply a subform filter where the filter on field is a partial string of the se

[gnawoncents]

Hi folks! I'm hoping someone can help me with this one (either with a solution or telling me it's impossible so I stop wasting time trying).

I have a subform that filters based on a text box on the main form. This works fine so long as the fields match. I, however, need to find results when the subform field is an exact match to part of the search field. For example:

The search text might be "ONE" and will correctly find all records where the applicable subform field is "ONE".

The problem is when the search contains multiple elements. For instance, a search for "ONE, TWO" needs to return all records where the applicable subform field is "ONE" OR "TWO", not just an exact match to "ONE, TWO".

I know how to do this in reverse, but can't figure out the way to set it up how I need. I have tried setting a filter in the Record Source of the subform and applying it using an after update VBA event, both with no joy.

Below is a snippet of a few code variations I have tried to use to apply the filter in the after update event. Thanks in advance for any help.

Forms.fmTrainingReport.fmMemberItemsReport.Form.Filter = "[fldFullName] Like '" & Me.txtMemberFilter & "'"
 
Forms.fmTrainingReport.fmMemberItemsReport.Form.Filter = "*[fldFullName]* Like '" & Me.txtMemberFilter & "'"
 
Forms.fmTrainingReport.fmMemberItemsReport.Form.Filter = "'*' & [fldFullName] & '*' Like '" & Me.txtMemberFilter & "'"
 

[Seth Schrock]

If you are looking for an exact match of "ONE" or "TWO", then use the In statement instead of the = or Like statements.Field_Name IN ("ONE", "TWO")

[gnawoncents]

Thanks for the help. I'm not sure, however, that this will work for me since my result is a single string (e.g. "ONE, TWO, THREE" as opposed to multiple items (e.g. "ONE","TWO","THREE"). Am I missing something?

[Seth Schrock]

You are correct that you won't be able to directly reference the textbox in the filter string. However, if the textbox has the different search string separated by a unique character (such as a comma) that won't ever be part of the search text, then you can use the Split() function to separate the different search strings into an array. Then add the quotes around the different values, and then use the Join() function to put all the parts back together with a separating comma and then you have your search string for the IN statement.

[gnawoncents]

Thanks for the direction. I wasn't considering an array, but that should work.

[Seth Schrock]

The other option would be to use the Replace() function to replace the delimiting character with "," and then put a double quote at the beginning and ending of the string.

Dim strFilter As String
strFilter = Chr(35) & Replace(Me.txtMemberFilter, ",", Chr(35) & "," & Chr(35)) & Chr(35)

[jforbes]

IN() is great if you are looking to match the entire field to a value in a List.

If you are looking to use the LIKE operator, then you'll need to write some code to split the String and build up a WHERE clause, for example:

Public Function parseWhere(ByRef sWHERE As String, ByRef sFieldName As String) As String
    Dim oWhere As Variant
    For Each oWhere In Split(sWHERE, ",")
        parseWhere = parseWhere & " OR " & sFieldName & " LIKE '*" & Trim(oWhere) & "*'"
    Next oWhere
    If Len(parseWhere) > 0 Then parseWhere = Right(parseWhere, Len(parseWhere) - 4) 
End Function

This would be the Immediate Window:

?parseWhere("one, two, three", "[fldFullName]")
[fldFullName] LIKE '*one*' OR [fldFullName] LIKE '*two*' OR [fldFullName] LIKE '*three*'

So the following should work:

Forms.fmTrainingReport.fmMemberItemsReport.Form.Filter = parseWhere(Me.txtMemberFilter, "[fldFullName]"

[gnawoncents]

jforbes, thank you! This works perfectly.