Search on single word within a string

[Squeekykilt]

Hi All,

Have a slight problem, I am trying to create a search form for an access database which searches for a word (or several words) within a string of text held in one of the tables rows.

The code I have so far is as follows;

DoCmd.ShowAllRecords
DoCmd.GoToControl ("str_Problem")
DoCmd.FindRecord Me!txtsearch

str_problem.SetFocus
strProblemRef = str_problem.Text
txtsearch.SetFocus
strSearch = txtsearch.Text

'If matching record found sets focus in str_Problem and shows msgbox
'and clears search control

If strProblemRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
str_problem.SetFocus
txtsearch = ""


However my problem is that this is only searching for records where the text in "str_problem" matches the search criteria exactly so it will find a record with "printer" as the text in the "str_problem" field but not those which read "Printer is not working".

Any suggestions of how I can search on a single word within a bigger string?

 

[KeithG]

There are several way this can be done. You could write an SQL statement or you could step through the string and search for your value.

 

[FireStrike]

I used the following code to find comma's in a string and remove them, but you can use something similar in your case.

<code>
While InStrRev(arrData(3), ",")
DoEvents
arrData(3) = Mid(arrData(3), 1, (InStrRev(arrData(3), ",") - 1)) & Mid(arrData(3), (InStrRev(arrData(3), ",") + 1))
Wend
</code>

if you change the
While InStrRev(arrData(3), ",")
to
While InStrRev (strProblemRef, strSearch)
then it should work for you. This will actually loop through and find every instance of your strSearch, you can change it to an if statement if you only need to find if your strSearch is there, and don't care how many times it is there.

 

[Squeekykilt]

This will actually loop through and find every instance of your strSearch, you can change it to an if statement if you only need to find if your strSearch is there, and don't care how many times it is there.

Thanks FireStrike, I think that may well do waht I need it too, though it will probably better to use the IF statement as you suggest as I only need to know if the word is there.

Another thouhgt has occured to me, there will likely be multiple records in some cases with the searched word present in them, what is the most effective method of telling access to display multiple finds in a table or form?

(Apologies for may ignorance, I am still fairly inexperienced in the world of VBA!)

Any ideas anyone???

Thanks