searching multiple fields in Access

Question

i have a table that contains a text field where I type in different notes. I have a form that I would like to use as a search form for that field. Can this be done? I design mode on the query it works fine but on the form it doesnt. i try using the "like" control and wild card, but to no avail. if anyone could give me advice i would greatly appreciate it. Thank you

Answer 1

You might like to take a look at FindWords.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates the use of a FindWords function, along with two auxiliary functions, FindAllWords and FindAnyWord.  These functions will find any word or phrase in a column in a table.  It will only find true words or phrases, with no specious substring matches, which can occur with the use of the LIKE operator with wildcards.

The demo uses T S Eliot's great poem The Wasteland, storing each line as a separate line in a table,  as its example.

Answer 2

First, let me echo John's advice about a Notes table. Especially if you want to search in the field.

Second, you are on the right track. You can create a filter. Use an unbound text box to enter the search text you want to fine, Then, in the After Update event use code like:

Dim strFilter as String

strFilter = "fieldname LIKE '*" & Me.controlname & "*'"

DoCmd.RunCommand ac CmdApplyFilterSort

Answer 3

This is quite possible, but without knowing the structure of your tables or any examples it's hard to suggest a specific solution. Could you post an example of what you've tried, and what you're trying to search?

One concern is that you may be trying to store multiple notes jammed together in one big Long Text field. Such a field can be searched but it makes it much more difficult. A better design is to have a one-to-many relationship between your main table and a Notes table; each record of the latter would contain just one note (probably along with fields for the date/time the note was generated and who created the note).

Answer 4

Your subject line says "multiple fields", and the body talks about "a text field". That seems a contradiction. Can you explain?