Search over multiple fields.. 2

[garty]

I have a "fault log" database that contains a table (tblFaultDetails) with (amongst others) fields 'Fault Summary' (TEXT - 150 characters) and 'Fault Details' (MEMO).

I also have a seperate table (tblAdditionalComments) that feeds additional/extra comments into the original call (for updating purposes). Field name 'Comment' (MEMO).

I want to create a report or form that will prompt for a word/phrases to search for and will look for that instance in all of the above fields, returning all instances of the typed word.

As far as I can tell, Microsoft's FIND function will search whatever table you specify, displaying one record. You then have to search for next record. I want to display all records in one hit.

Any ideas anyone???

 

[LarryDeLaruelle]

Garty:

Create a query based on your tables and include the fields you need.

Create a report based on the query.

Create an unbound form with a control (text box) to allow the users to enter the string criteria and a command button to open the report.

For the example, the form is named frmSearch and the field is called txtString.

Back to the query; in the criteria cell of FaultSummary enter:

Like "*" & Forms!frmSearch.txtString & "*"

This merely says, look for any record that has the value of txtString anywhere in the field FaultSummary. The asterisks are wild cards so that it will look at the entire field and not just the beginning or end.

On the next criteria line enter the same code in the cell for FaultDetails. And, on a third criteria line, in the cell for Comments enter the same code.

It sounds like circular logic, but the command button will open the Report which will kick off the query. The query will reference txtString on your form for the criteria.

Putting the criteria on separate lines for each field creates an OR condition. It should find any instance of the search string in any of the fields.

One note of caution, string searches in memo fields can be problematic. I've tested such searches successfully but that may be a function of the size of the memo field (mine were all short).

Hope this helps.
Larry De Laruelle
larry1de@yahoo.com

 

[garty]

Larry, you are a star!!!

This works a treat. It provides all occurances of the "search" even in the memo fields (mind you they are not huge (yet).

Sorry for the delay in responding but I had been off for a few days.

Thanks again Larry.

Garty

 

[LarryDeLaruelle]

Garty:

Happy I could help.

When I was first starting with Access I received much invaluable assistance from the folks here at Tek-Tips.

Being able to assist others, now that I'm a bit more experienced, is a pleasure.

I say a bit more experienced since everytime I begin to think I know what I'm doing, I bump into something that brings me down to earth.

Good luck.
Larry De Laruelle
larry1de@yahoo.com