Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Wildcard Search in multiple fields

Status
Not open for further replies.

GelC

Technical User
Oct 3, 2006
93
US
I have a command button to perform record filter attached to the following code
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:
Code:
    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
 
You could try concatenate all of the fields together:
Code:
    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
 
Work likes a charm.
Thank you! dhookom.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top