Search mulitple fields

[wgroenewald]

Hi there

I have a form that I'm using to search a table, and its working pretty well.
Each field on the table has its own search field and I hit the search button and off it goes.

What I would like to do though is create one search text box that searches multiple fields on the table.

This is the current code:

 Option Compare Database
Option Explicit
Private Sub Clear_Click()
    DoCmd.Close
    DoCmd.OpenForm "Search Issues"
End Sub
Private Sub Search_Click()
    Const cInvalidDateError As String = "You have entered an invalid date."
    Dim strWhere As String
    Dim strError As String
    
    strWhere = "1=1"
     
    ' If Assigned To
    If Not IsNull(Me.AssignedTo) Then
        'Create Predicate
        strWhere = strWhere & " AND " & "Issues.[Assigned To] = " & Me.AssignedTo & ""
    End If
    
    ' If Opened By
    If Not IsNull(Me.OpenedBy) Then
        'Add the predicate
        strWhere = strWhere & " AND " & "Issues.[Opened By] = " & Me.OpenedBy & ""
    End If
    
    ' If Status
    If Nz(Me.Status) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Issues.Status = '" & Me.Status & "'"
    End If
    
     ' If Category
    If Nz(Me.Category) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category & "'"
    End If
    
     ' If Priority
    If Nz(Me.Priority) <> "" Then
        'Add it to the predicate - exact match
        strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority & "'"
    End If
      
    ' If Opened Date From
    If IsDate(Me.OpenedDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " & GetDateFilter(Me.OpenedDateFrom)
    ElseIf Nz(Me.OpenedDateFrom) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Opened Date To
    If IsDate(Me.OpenedDateTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " & GetDateFilter(Me.OpenedDateTo)
    ElseIf Nz(Me.OpenedDateTo) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' If Due Date From
    If IsDate(Me.DueDateFrom) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Issues.[Due Date] >= " & GetDateFilter(Me.DueDateFrom)
    ElseIf Nz(Me.DueDateFrom) <> "" Then
        strError = cInvalidDateError
    End If
    
     ' If Due Date To
    If IsDate(Me.DueDateTo) Then
        ' Add it to the predicate - exact
        strWhere = strWhere & " AND " & "Issues.[Due Date] <= " & GetDateFilter(Me.DueDateTo)
    ElseIf Nz(Me.DueDateTo) <> "" Then
        strError = cInvalidDateError
    End If
    
    ' 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
    
        ' If Comments
    If Nz(Me.CommentSearch) <> "" Then
        ' Add it to the predicate - match on leading characters
        strWhere = strWhere & " AND " & "Issues.Comment Like '*" & Me.CommentSearch & "*'"
    End If
  
    
    If strError <> "" Then
        MsgBox strError
    Else
        'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
        If Not Me.FormFooter.Visible Then
            Me.FormFooter.Visible = True
            DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
        End If
        Me.Browse_All_Issues.Form.Filter = strWhere
        Me.Browse_All_Issues.Form.FilterOn = True
    End If
End Sub
Function GetDateFilter(dtDate As Date) As String
    ' Date filters must be in MM/DD/YYYY format
    GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function

I've already created the new search text box called txtMultiple, but how do I change the above code to make it search Issues.Comment, Issues.Title, Issues.Category?

I would appreciate any assistance.

Many thanks.

[orange]

Free video here http://www.datapigtechnologies.com/f...earchform.html