How to compare 1 field against many using IF OR?

[VBANOOBY]

OK my first post so please be patient with me, and thanks in advance for any and all help!

I have an Access 2007 database where I want the user to be able to type a few charactors into a field and then compare (search) several other fields to return all records that contain those typed-in charactors from those several other fields. Currently, I use this VBA code to compare the input field with the same field in the database, BUT, I want to compare the Me.Title field to 4 other fields as well, but I keep screwing up the Syntax and can't get it right.

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title & "*'"
End If

How would I do this? Using IF OR statements with OR on the next line, or a string of AND statements? I think I need to use OR because my understanding of the string of AND statements means it will return the first field found matching and stop searching, when I need to acturally search and return ALL records that match ANY of the searched fields.

So, I need to compare Me.Title to Me.Title, Me.Person(s) Present / Title, Me.Outcome / Result, Me.Actions / Follow Up, Me.Red Flag(s).

I know you pros have probably answered this a million times, but I cannot seem to find the answer.

 

[MSAccessRookie]

The following code returns values when any of the conditions is met and could be a start toward what you are looking for. If you want values when ALL of the conditions are met, you wil need to change the OR Statements to AND Statements.

[COLOR=black][FONT=Verdana]If Nz(Me.Title) <> "" Then ' Add it to the predicate - match on leading characters
    strWhere = strWhere & " AND " & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        (("Issues.Title Like '*" & Me.Title & "*'") OR " & _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]         ("Field2.Title Like '*" & Me.Title & "*'") OR " & _
         ("Field3.Title Like '*" & Me.Title & "*'") OR " & _
         ("Field4.Title Like '*" & Me.Title & "*'"))
End If[/FONT][/COLOR]

 

[VBANOOBY]

MSAccessRookie, you are correct, I want to view records if ANY of the conditions are met and therefore, the OR statement is the best way to do this. I edited my code to the following but I get a "Syntax error" when it trys to run:

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & _
(("Issues.Title Like '*" & Me.Title & "*'") OR " & _
("Persons Present / Title.Title Like '*" & Me.Title & "*'") OR " & _
("Outcome / Result.Title Like '*" & Me.Title & "*'") OR " & _
("Actions / Follow Up.Title Like '*" & Me.Title & "*'"))
End If

Am I typing something incorrectly? Thank you for your help and precious time just before the holidays!

 

[MSAccessRookie]

MSAccessRookie, you are correct, I want to view records if ANY of the conditions are met and therefore, the OR statement is the best way to do this. I edited my code to the following but I get a "Syntax error" when it trys to run:

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & _
(("Issues.Title Like '*" & Me.Title & "*'") OR " & _
("Persons Present / Title.Title Like '*" & Me.Title & "*'") OR " & _
("Outcome / Result.Title Like '*" & Me.Title & "*'") OR " & _
("Actions / Follow Up.Title Like '*" & Me.Title & "*'"))
End If

Am I typing something incorrectly? Thank you for your help and precious time just before the holidays!

I assume that Persons Present / Title, Outcome / Result, and Actions / Follow Up are the names of the Tables/Queries that create the Datasets that you are using. If that is the case, then your Table Names will need to be surrounded by Brackets (Like [Persons Present / Title]), because of the Spaces and other Non-AlphaNumeric characters contained in them. A better idea (if you have the ability) would be to rename the Tables to eliminate inappropriate Characters.