I have created a search field in MS Access but it requires exact input?

Question

Is there anyway I can create a search that searches for part of the word and then filters the records to match. For example:

My search is based in a text box which has a macro attached to it:

="Description = '" & Forms![frmCategories]![txtDescription] & "'"

It works fine, but it will only bring up the record if you have typed in the full item correctly. Is there any way i could type in, for instance, "Daniel" and it will show me just the records that contain Daniel?

Answer 1

As others here have mentioned...use LIKE in your SQL query.



The LIKE condition allows you to use wildcards in the WHERE clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.



The patterns that you can choose from are:

  * allows you to match any string of any length (including zero length)

  ? allows you to match on a single character

  # allows you to match on a single numeric digit



Examples:

="Description LIKE '" & Forms![frmCategories]![txtDescription] & "*'

="Description LIKE '*" & Forms![frmCategories]![txtDescription] & "*'

="Description LIKE '*" & Forms![frmCategories]![txtDescription] & "'

="Description LIKE '" & Forms![frmCategories]![txtDescription] & "?'

="Description LIKE '" & Forms![frmCategories]![txtDescription] & "#'



Good luck!

Answer 2

If you are creating a query and want to bring up, say the word 'Wednesday' whether it appears by itself or in a string of words, use *Wednesday* so that tells the computer find every record that contains the word Wednesday whether it begins or ends with anything else. Basic searching, you use the asterisks as wild cards.

Answer 3

I assume you are using SQL for your search. In your select statement use the word "LIKE" instead of "=". You can use "?" as a wildcard. If you want to ignore case then covert both parameters to either uppercase or lowercase.