Search Question - Multiple boxes, multiple fields

[offie]

I have a table that has 4 fields with words in them.
I want to be able to enter key words to search through all of the fields.
I have 3 text boxes to enter search key words.

This is what I came up with

WHERE 
(((LessonInformation.LongDescription) Like "*" & [forms]![Home]![SearchByWord]![SKW1] & "*" And (LessonInformation.LongDescription) Like "*" & [forms]![Home]![SearchByWord]![SKW2] & "*" And (LessonInformation.LongDescription) Like "*" & [forms]![Home]![SearchByWord]![SKW3] & "*")) 

OR (((LessonInformation.ShortDescription) Like "*" & [forms]![Home]![SearchByWord]![SKW1] & "*" And (LessonInformation.ShortDescription) Like "*" & [forms]![Home]![SearchByWord]![SKW2] & "*" And (LessonInformation.ShortDescription) Like "*" & [forms]![Home]![SearchByWord]![SKW3] & "*")) 

OR (((MasterSpec.Description) Like "*" & [forms]![Home]![SearchByWord]![SKW1] & "*" And (MasterSpec.Description) Like "*" & [forms]![Home]![SearchByWord]![SKW2] & "*" And (MasterSpec.Description) Like "*" & [forms]![Home]![SearchByWord]![SKW3] & "*")) 

OR (((MasterSpec_1.Description) Like "*" & [forms]![Home]![SearchByWord]![SKW1] & "*" And (MasterSpec_1.Description) Like "*" & [forms]![Home]![SearchByWord]![SKW2] & "*" And (MasterSpec_1.Description) Like "*" & [forms]![Home]![SearchByWord]![SKW3] & "*"));

But this makes it so that the key words have to be in the same field...
Ex: field1: metal railing, field2: masonry
If I search for metal and masonry I will get no results
If I search for metal and railing I will get results



I think this is simple but I'm having a hard time coming up with an alternative.

Thanks!

[June7]

You mean you have a query that has 4 fields with words in them? That WHERE shows 3 tables as source, not a single table. It looks like MasterSpec is included in the source query twice - why?

Do LessonInformation and MasterSpec have a relationship?

Do any records ever have null in any of the fields?

[orange]

I worked with a poster "meep" who was looking for something similar.
The discussion is at
https://www.accessforums.net/queries...09/index4.html and is fairly long. If you follow the posts you will see the questions and answers that lead to the database with the search.

The sample database - I'm not sure how experienced you are - is in post#50 at
https://www.accessforums.net/queries...index4.html#50

[offie]

I decided to just add all the text strings into one field, make it not visible, and then search through that.

Field: [LessonInformation].[LongDescription] & [LessonInformation].[ShortDescription] & [MasterSpec].[Description] & [MasterSpec_1].[Description]

criteria: like "*" & [forms]![Home]![SearchByWord]![SKW1] & "*" And Like "*" & [forms]![Home]![SearchByWord]![SKW2] & "*" And Like "*" & [forms]![Home]![SearchByWord]![SKW3] & "*"

[June7]

Terrific idea but you might want to concatenate a space between each. Also, Field is a reserved word so use something like FieldsComb.

[LongDescription] & " " & [ShortDescription] & " " & [MasterSpec].[Description] & " " & [MasterSpec_1].[Description]

[offie]

Thanks, forgot to do that, and since it's not visible it doesn't need a name I was just specifying that it was in the field row.

[orange]

Glad you have a solution