Searching for a word within a record

[TomJamieson]

I am trying to create a form that will search my table for any records where a specified word appears in a specified field. Both searchable fields are memo fields, and can have lots of words in.

So what I want is for the user to be able to select field1 from a combo box, then type in "word" and any records with the word "word" anywhere in the field1 field will be displayed.

Does anybody know how this could be achieved, I'd really appreciate the help!

 

[llkhoutx]

Use the INSTR function to locate a string within a field, Text or Memo.

Access Help articulates the arguments for the function.

 

[TomJamieson]

But that just tells me what position in the field that the searched word is. How do I utilise that to show an entire record based on if it appears at all?

 

[llkhoutx]

Use

Like Forms!YourFormName!YourComboBoxFieldName & "*"

at the criteria of the text or memo field in a query to display all records containing the character displayed in Forms!YourFormName!YourComboBoxFieldName.


This test will be case insensitive

 

[IR_Moon]

Use
Like Forms!YourFormName!YourComboBoxFieldName & "*"

Shouldn't that have a * in the front too? otherwise yer only searching the start of the field, no?

i.e.

Like "*" & Forms!YourFormName!YourComboBoxFieldName & "*"

 

[llkhoutx]

Not only a *, but also a blank before and after the word, otherwise the desired string within a word will be returned, i.e.

Use

Like "* " & Forms!YourFormName!YourComboBoxFieldName & " *"

 

[jdraw]

I think what you are really looking for is the SQL (query) to search the proper field in your table. Also, when you find the records containing the "word" you're searching for, you need to identify those records some how.

Suppose your table is called tstTable, and you have fields such as:

id --- a unique record identifier (autonumber data type)
name -- some other field not involved in the search and not identifying specific record

fld1 --- one of the choices to be searched
fld2 --- another of the choices to be searched


SELECT tstTable.id, tstTable.name, tstTable.fld1
FROM tstTable
WHERE fld1 Like '* moved *'; <--- my search term is "moved"

I believe llkhoutx is correct in the space before and after the "word" you are searching for.

You need a button or some control or event to execute the SQL after selecting the fld to besearched and the term to search for.

Hope this is helpful.