Filter with Keyword in Memo Field

[Njliven]

I need help in figuring out the best way to go about creating a way to filter a form based on a keyword in a memo field. I want the user to be able to click on a button and enter the keyword they want and then the records will filter and show all the records containing that particular keyword. Thanks for any help!

[orange]

Why is this in a memo field? Please tell us more.

You may get some ideas from this keyword search.

[Njliven]

Why is this in a memo field? Please tell us more.

You may get some ideas from this keyword search.

It is a memo field because it has a large amount of data concerning a project description and scope. The user needs to be able to find a specific word so they can find all the projects with all the same type data they are looking. This can be a wide variety of things.

[John_G]

Have you tried instr([Memo_field],keyword) > 0 ? (with your own variable and field names)

[Njliven]

It does not like the > sign, it says an = is expected. Also I am not sure how to make the variable so the user can input what they want to search. Does this code go in the buttons on click event?

[John_G]

You would have to use VBA to create a filter string, then re-filter the form. Suppose the textbox is called SearchWord; if you have a button to perform the search / filter, you could put something like this in the On_Click:

Dim FilterString as String

FilterString= "Instr([Memo_field]),'" & me!SearchWord & "' ) > 0"
docmd.applyfilter , FilterString

[redbull]

So on the button onclick, if you just want the user to be able to put in a Keyword or search term... and don't want a fancy form...


Dim Keyword as string

Keyword = inputbox("Please enter your keyword")

If instr([Memo_field],keyword) > 0 then
run whatever query you want with the keyword in it. Because it has a result.
else
msgbox "Didn't find a match"
End if

Or if you have a textbox on the main form, with a button... instead of the input box.. just

Keyword = me.forms.txtbox.value, or something like that.

[Njliven]

To: John_G
With this code I am getting a run time error. When I click to debug it has the last line highlighted
DoCmd.ApplyFilter , FilterString. I tried a couple of different things, but then when I click the button nothing happens.
What to do next?

[Njliven]

To redbull,
With this code you say I need a query, but I want to filter the form and bring back all the records with the entered keyword. Can this be done with a button only?

[Njliven]

I found a solution. I had two memo fields and I was going to make a button for each one, but I found code that would search and filter both fields and this is perfect for my situation. I added it to the buttons on click event and it works perfectly.

Dim MyValue As String
MyValue = InputBox("Enter Keyword")
Me.Filter = "[Memo_Field1] Like '*" & MyValue & "*' Or [Memo_Field2] Like '*" & MyValue & "*'"
Me.FilterOn = True

[orange]

Not sure what you have for data, but here is a sample from one of my tables. It uses the INSTR() construct suggested to you by the others.

I'm doing this with a query, but you could set up a form with similar concept. I have used " employee" as my keyword for this sample. You could use a txtbox to create your filter.

I am searching for a keyword/string within a memo field called "txt".
If found I return the Estblmt_No, the length of the memo field, the right most 20 characters of the memo field, and the momeo field.

SELECT tbl_Spl_LCL_Std_EstProfileEng.ESTBLMT_NO
, Len([TXT]) AS LENGTH
, Right([txt],20) AS Last20
, tbl_Spl_LCL_Std_EstProfileEng.txt
FROM tbl_Spl_LCL_Std_EstProfileEng
WHERE  instr(txt, " employee")>0

OOOPs: I see you have resolved it while I was typing.