Wildcard Search in multiple fields

[GelC]

I have a command button to perform record filter attached to the following code

Private Sub cmdSearch_Click()

Dim strWhere As String
Dim lngLen As Long

    If Not IsNull(Me.txtSongNumber) Then
        strWhere = strWhere & "([SongNumber] = Like ""*" & Me.txtSongNumber & "*"") AND "
    End If
    
    If Not IsNull(Me.txtTitle) Then
        strWhere = strWhere & "([Title] = Like ""*" & Me.txtTitle & "*"") AND "
    End If

    If Not IsNull(Me.txtAuthor) Then
        strWhere = strWhere & "([Author] = Like ""*" & Me.txtAuthor & "*"") AND "
    End If

    If Not IsNull(Me.txtKeywords) Then
        strWhere = strWhere & "([Keywords] = Like ""*" & Me.txtAuthor & "*"") AND "
    End If

Now, I just want to have only one text box to search in [SongNumber], [Title], [Author] and [Keywords]. How can I combine them into one statement.
I tried:

    If Not IsNull(Me.txtKeywords) Then
        strWhere = strWhere & "([SongNumber] OR [Title] OR [Author] OR [Keywords] = Like ""*" & Me.txtAuthor & "*"") AND "
    End If

It never works as I expected but gives me all records I have in my database.
Thanks for any help!

Gelsea

 

[dhookom]

You could try concatenate all of the fields together:

    If Not IsNull(Me.txtKeywords) Then
        strWhere = strWhere & "[SongNumber] & [Title] & [Author] & [Keywords] = Like ""*" & Me.txtAuthor & "*"" 
    End If

Duane
Hook'D on Access
MS Access MVP

 

[GelC]

Work likes a charm.
Thank you! dhookom.