How can I search part of text from a field in VBA?

[hanski]

Hi.

I have 2 fields. Both are text fields. First field includes a text
'Cat'. Second field includes a text 'Mouse Dog Cat Horse'.

I would like to find out if the text in first field includes in the in
secon field via VBA.

What kind of function I have to make or is there some other way to do
it?

Hannu

 

[Douglas J. Steele]

In VBA, you can use the InStr function:

Dim strField1 As String
Dim strField2 As String

strField1 = "Mouse Dog Cat Horse"
strField2 = "cat"
If InStr(strField1, strField2) > 0 Then
' strField2 appears in strField1
Else
' strField2 does not appear in strField1
End If

Of course, I'm assuming that your example is simpler than what you're
actually trying to do. That formula would also say that cat appears in the
string "Toss Scatters Strews". If all of your words are separated by spaces,
you could use

If InStr(" " & strField1 & " ", " " & strField2 & " ") > 0 Then
' strField2 appears in strField1
Else
' strField2 does not appear in strField1
End If

Another approach would be to use the Split function to take the contents of
strField1 and put each word in an array:

Dim booFound As Boolean
Dim lngLoop As Long
Dim strField1 As String
Dim strField2 As String
Dim varWords As String

strField1 = "Mouse Dog Cat Horse"
varWords = Split(strField1, " ")
strField2 = "cat"
For lngLoop = LBound(varWords) To UBound(varWords)
If varWords(lngLoop) = strField2 Then
booFound = True
Exit For
End If
Next lngLoop
If booFound = True Then
' strField2 appears in strField1
Else
' strField2 does not appear in strField1
End If