Keyword Searches in Access Databases

[greeneyerat]

Is there an available feature that would enable an end-user to conduct a keyword search through an Access Database?

Best regards,
JM

 

[johnwm]

Use a Query perhaps:

SELECT tblClients.ClientName
FROM tblClients
WHERE ((tblClients.Town) Like '*' & [Town] & '*')

When the query runs it prompts Town? for part of the Town field and then returns all clients names whose Town field contains the reply to the prompt.

A reply of al will return records relating to alfreton, maldon and donegal

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

For tsunami relief donations

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[willir]

JM

If I understand you correctly, you want to search "all" of the database -- is this correct?

For example:
For a contact database. You want to search for "John" for
- first name, lastname (Johnson), streets, cities, etc.

Data in Access is stored in tables. Tables are very different than storing a word processing document in one open-text file.

This would require looping through each table, and then searching through most of the fileds for the data you are looking for per the a method similar to the way described by johnwm]/b].

This is not that easy to do, and may be pretty time consuming -- waiting for the "hits".

If you search for a field that is not indexed, the search can be pretty slow, especially if there are a lot of records. If you index everything, then you database will grow in size.

And the coding would be a bit tricky.

Suggestions:
Decide what you want to look for -- being more specific will be much easier and much more effecient.

Using unbound combo and list boxes on a form is a common approach. The wizard will do most of the work for you.

An alternative would be to create a form designed for the "search". Include a multi-select list box with a value list of fields / tables you want to search. Once the search form has been completed, VBA coding would create the query or queries requried to execute the search based on items selected in the list box.

If you are going to search multiple fields for the same item, then you will have to do several things...

- Probably a good idea to search common fields. If you are searching for a text string, you don't want to include number fields or date fields. If you are searching date fields, there is probably no reason to include searches in text and definitely not in numeric fields.

- Are the fields similar so that you can take advantage of a "union" query? Using a Union query is to your advantage if you have to search multiple tables. Look up Union queries on the web or within Access Help. Note that you can not use the query builder to create a Union query.

- How are you going to present your data? If you can create one query for the search, then you should be able to present the data in a simple query listing / worksheet. If you can not use one query to complete the search, then you may have to use a TEMP table to store your retrieved results for each query, and execute each query and append the retrieved information to the end of the TEMP table -- do-able, but more work.

Richard

 

[harebrain]

So if you really want to do this, the answer is to step up to a database that does full-text indexing, such as SQL Server.