Extract a word from a string

[moishy]

I need a function that will accept a string and return one word from that string, the word I'm looking to extract is one of 13 words.

All help will be appreciated.

 

[plog]

My suggestion is to write out instructions as if you are going to assign this task to a third grader. Use simple english, cover all cases and work as linearly as possible. When you have that, you need to translate those instructions to VBA. Most likely you will need to use the substring function Mid (http://www.techonthenet.com/access/functions/string/mid.php) and the string position identification function InStr (http://www.techonthenet.com/access/functions/string/instr.php)

If you need specific help, post what you've done so far and where you are having trouble.

 

[Alansidman]

Possible to do with query. Possibly with the Instr function in the criteria.

http://www.techonthenet.com/access/functions/string/instr.php

 

[pr2-eugin]

More information is required.. Like what is the word? What will be the string? How long will be the string?

But basically..

Public Function extractWord(theString As String, theWord As String) As String
    Dim wordLoc as Integer
    wordLoc = InStr(theString, theWord)
    If wordLoc <> 0 Then
        extractWord = Mid(theString, wordLoc, Len(theWord))
    Else
        extractWord = "False"
    End If
End Function

Check the calling method to see if it does not equal to False.

 

[moishy]

Thank you all for your responses.

1. The length of the string is unknown (i.e. will change from time to time).
2. The word to be extracted will always exist and will be one of 13 possible values (for example: blue, black, brown, burgundy, white, yellow, orange, cyan, pink, red, green, grey, beige etc.).

 

[Brianwarnock]

He has said that there are 13 words so an if block of 13 tests will do it

Public Function extractword(thestring) as string

If instr([thestring],"firstword")<>0 then
Extractword = "firstword"
ElseIf
Etc

Brian