Search field

[wgroenewald]

Hi there



I downloaded a template from Microsoft which allows for issues to be logged and stored in the database. Issues can then be edited, and search.

The search form in the database does well, but I need to include another search.

In the database I've created a CommentsSearch Text box in the Issues Search form. What I want this to do is search the Comments field in the Issues table and return any row where the Comments field in the Issue table contains the text given in the CommentsSearch text box.

When I hit search on the Issues Search form it should then return the results the same way it currently returns results when using the search fields already on the template.

Can anyone assist?

Here is the code that is currently triggered when hitting the Search button:

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 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

Thanks so much

If need be I can pm/email the database. It's 1.5mb big.

WG

[June7]

Can zip the project, 2mb zip file attachment to post allowed.

Is the comment field text or memo type?

[wgroenewald]

Hi

Thank you so much for your response.
Please have a look at the attached and let me know what you think.

So much appreciated
WG