VB: Check if a string contains a specific word

Using VB on an Access Table, I want to select some field if the field value which is a string of 3 or 4 words has the third word or the fourth word be the word "Spot".  How do I check this?

One way may be to find the second blank space character, take the Right of that to crop out that space and everything before that and then do a Like "Spot*"?

What would be good VB code for this?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
Perhaps some combination using INSTR?

Or perhaps SPLIT the string into a variable array and check the 3rd and 4th variables for that string?
IF Instr(yourStr,"TheWord") > 0 then
  msgbox "Itsthere"
 msgbox "Not there"
end if

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Will this help you any, Copy and paste, Call the Function IsSpotThere with the sentence to check
It will return the value below, just check this value.


Function IsSpotThere(strWord) As Integer

'returns 0 if Spot is not in the 3rd or 4th Word
'returns 1 if Spot is in the 3rd Word
'returns 2 if Spot is in the 4rd Word
'returns 3 if Spot is in the 3rd and 4th Word

Dim Arr_Word

' Remember Split is Zero Based
Arr_Word = Split(strWord, " ")

IsSpotThere = 0

If UBound(Arr_Word) > 1 Then If InStr(1, UCase(Arr_Word(2)), "SPOT") > 0 Then IsSpotThere = 1
If UBound(Arr_Word) > 2 Then If InStr(1, UCase(Arr_Word(3)), "SPOT") > 0 Then IsSpotThere = IsSpotThere + 2
End Function

Here is an example using Split

    Dim sText As String
    Dim sFind As String
    Dim iWord As Integer
    sText = "Now is the time for all good men to come to the aid of their country."
    sFind = "the"
    If MatchWord(sText, sFind, 4) Then
        MsgBox "found word"
    End If

Function MatchWord(sText As String, sFind As String, iWord As Integer) As Integer
    Dim sWord() As String
    Dim iFound As Integer
    sWord = Split(sText, " ")
    iFound = False
    Select Case iWord - 1
        Case 0 To UBound(sWord)
            iFound = CBool(UCase$(Trim$(sWord(iWord - 1))) = UCase$(Trim$(sFind)))
    End Select
    MatchWord = iFound
End Function

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

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.