Access 2003 Reports

[mbaldw]

Hi all,

does anyone know whether it's possible to create a query or report in Access (either XP or 2003) based on a search string?   I have an Access database containing several thousand articles and I want to generate a report showing all the articles that contain two or three words in the title.   All the tutorials I have found so far show how to generate reports based on specific categories in the database (so say, all those articles published in a particular journal in a particular year), but none mention using a specific search string.   Any ideas?

Cheers,
Marc.

[scuzzy]

Do you want to search titles that contain "x" number of words? For example, searching titles that contain a total of 3 words.

Or do you want to search titles for specific words, such as "taxes" and "report"?

[mbaldw]

Hi scuzzy, thanks for the response.   I want to search for records containing two different words.   For example, all the records where "apple" and "orange" are present in the "Fruit Name" field.   So, if in my fruit name field I had an entry that said: "Apples, oranges and pears were delivered to the Restaurant on Wednesday" it would get included in the results.

Do you know if this is possible?   If not, can it be done in Excel (I could always export the database to an XLS file if that would save me some aggro).

Cheers,
Marc.

[scuzzy]

I apologize for the delay in getting back to you.

To begin with, I recommend that you change the search default from "Fast Search" to "General Search". You'll find the settings under Tools>>>Options>>>Edit/Find. This way, Access will search for any word matching your criteria. If you leave it at the default setting, Access will only look for an exact "all or nothing" match. However, this setting only applies when doing a search from within a form.

You can create a simple formula for a query or report. I always ran reports off of a query. I have not used Access in about 5 yrs, but the search string in the query criteria will be as the following example (you must use the quotes and asterisks as shown):

Code Select Expand


Like "*apple*" Or Like "*orange*" Or Like "*melon*"


Running a query with that criteria, under the appropriate field, will yield all records with any of the quoted words anywhere inside the field. The asterisks are necessary to provide a wild search string. Otherwise the formula will only search for exact matches; not inclusive as you require.

The SQL for the above will look as follows:

Code Select Expand


SELECT [Table Name].Field Name
FROM [Table Name]
WHERE ((([Table Name].Field Name) Like "*apple*" Or ([Table Name].Field Name) Like "*orange*" Or ([Table Name].Field Name) Like "*melon*"));


[mbaldw]

Fantastic - thanks for that scuzzy.   I'll give it a shot this weekend.

Cheers,
Marc.