By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

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

gnawoncents
100+
P: 212
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.

Expand|Select|Wrap|Line Numbers
  1. Forms.fmTrainingReport.fmMemberItemsReport.Form.Filter = "[fldFullName] Like '" & Me.txtMemberFilter & "'"
  2.  
  3. Forms.fmTrainingReport.fmMemberItemsReport.Form.Filter = "*[fldFullName]* Like '" & Me.txtMemberFilter & "'"
  4.  
  5. Forms.fmTrainingReport.fmMemberItemsReport.Form.Filter = "'*' & [fldFullName] & '*' Like '" & Me.txtMemberFilter & "'"
  6.  
Feb 16 '17 #1

✓ answered by 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.

Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
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")
Feb 16 '17 #2

gnawoncents
100+
P: 212
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?
Feb 16 '17 #3

Seth Schrock
Expert 2.5K+
P: 2,931
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.
Feb 16 '17 #4

gnawoncents
100+
P: 212
Thanks for the direction. I wasn't considering an array, but that should work.
Feb 16 '17 #5

Seth Schrock
Expert 2.5K+
P: 2,931
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.
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  2. strFilter = Chr(35) & Replace(Me.txtMemberFilter, ",", Chr(35) & "," & Chr(35)) & Chr(35)
Feb 16 '17 #6

jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function parseWhere(ByRef sWHERE As String, ByRef sFieldName As String) As String
  2.     Dim oWhere As Variant
  3.     For Each oWhere In Split(sWHERE, ",")
  4.         parseWhere = parseWhere & " OR " & sFieldName & " LIKE '*" & Trim(oWhere) & "*'"
  5.     Next oWhere
  6.     If Len(parseWhere) > 0 Then parseWhere = Right(parseWhere, Len(parseWhere) - 4) 
  7. End Function
This would be the Immediate Window:
Expand|Select|Wrap|Line Numbers
  1. ?parseWhere("one, two, three", "[fldFullName]")
  2. [fldFullName] LIKE '*one*' OR [fldFullName] LIKE '*two*' OR [fldFullName] LIKE '*three*'
So the following should work:
Expand|Select|Wrap|Line Numbers
  1. Forms.fmTrainingReport.fmMemberItemsReport.Form.Filter = parseWhere(Me.txtMemberFilter, "[fldFullName]"
Feb 16 '17 #7

gnawoncents
100+
P: 212
jforbes, thank you! This works perfectly.
Feb 16 '17 #8

Post your reply

Sign in to post your reply or Sign up for a free account.