Filter string in access

I have a combo that works with a case statement in access vba to take the appropriate filter action:

 Forms!cw_client_matching_form.Filter = "([ComparitorType] =2)" & StrFilterDate

I had to make a structural strange and [comparitorType] could now contain numerous values ie

1,4
1,7

etc as opposed to one numerical value so how do i change the filter string to reflect that?

like "*2*" ?



if as in my ex
PeterBaileyUkAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
It is really NEVER a good idea to store one piece of information in a field!

Well, the problem with "*2*" is that it would evaluate to True for 2, 12, 20, 21, ..29, 32,...  and testing for *,2* or *2," will return basically the same set, although it would potentially rule out either the first of last entry in the list

Is the field type of [ComparitorType] now a string?  If so, you could write a function and call that, something like:

Public Function fnStringContains(TestValue as string, TestAgainst as Variant) as boolean

   'TestAgainst is a variant so that if you pass a NULL value from your field, it can be handled properly

   Dim myArray as variant()
   Dim intLoop as integer

   'Default return value is False
   fnStringContains = False

   if isnull(TestAgainst) Then Exit Function

   'parse TestAgainst on commas
   myArray() = Split(TestAgainst, ",")
   
   'Check each element of myArray
   for intLoop = lbound(myArray) to ubound(myArray)
       if myArray(intLoop) = TestValue Then Exit For
   Next

End Function

You would then call this function something like:

Forms!cw_Client_Matching_Form.Filter = "fnStringContains('2', [ComparitorType]) & strFilterDate

0
 
Dale FyeCommented:
That should have read:

It is really NEVER a good idea to store MORE THAN ONE PIECE of information in a field!
0
 
PeterBaileyUkAuthor Commented:
this:  myArray() = Split(TestAgainst, ",")  gives type mismatch

?fnStringContains("1","1,2")
Public Function fnStringContains(TestValue As String, TestAgainst As Variant) As Boolean

   'TestAgainst is a variant so that if you pass a NULL value from your field, it can be handled properly

   Dim myArray() As Variant
   Dim intLoop As Integer

   'Default return value is False
   fnStringContains = False

   If IsNull(TestAgainst) Then Exit Function

   'parse TestAgainst on commas
   myArray() = Split(TestAgainst, ",")
   
   'Check each element of myArray
   For intLoop = LBound(myArray) To UBound(myArray)
       If myArray(intLoop) = TestValue Then Exit For
   Next

End Function

Open in new window

0
 
Dale FyeCommented:
Try this, it didn't like myArray() being defined as variant.
Public Function fnStringContains(TestValue As String, TestAgainst As Variant) As Boolean

   'TestAgainst is a variant so that if you pass a NULL value from your field, it can be handled properly

   Dim myArray() As String
   Dim intLoop As Integer

   'Default return value is False
   fnStringContains = False

   If IsNull(TestAgainst) Then Exit Function

   'parse TestAgainst on commas
   myArray() = Split(TestAgainst, ",")
   
   'Check each element of myArray
   For intLoop = LBound(myArray) To UBound(myArray)
       If myArray(intLoop) = TestValue Then
            fnStringContains = True
            Exit For
       End If
   Next

End Function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.