Search a for a specific keyword in the summary

[andro007]

Hi guys,

Just learnt this 2 days ago. I can't seem to figure out how to proceed.

This is my code.

Private Sub txtFindArticleIssue_AfterUpdate()
    Call CheckFilter
End Sub
 
Private Sub txtFindKeyword_AfterUpdate()
    Call CheckFilter
End Sub
 
Private Sub txtFindCreationDate_AfterUpdate()
    Me!txtFindCreationDate = IIf(IsDate(Me!txtFindCreationDate), _
                                 Format(Me!txtFindCreationDate, "d mmm yyyy"), _
                                 "")
    Call CheckFilter
End Sub
 
Private Sub cboFindDomain_AfterUpdate()
    Call CheckFilter
End Sub
 
'CheckFilter produces the new Filter depending on the values currently in
'txtFindArticleIssue, txtFindCreationDate, txtFindKeyword & cboFindDomain.
Private Sub CheckFilter()
    Dim strFilter As String, strOldFilter As String
    strOldFilter = Me.Filter
 
    'txtFindArticleIssue - Text
    If Me!txtFindArticleIssue > "" Then _
        strFilter = strFilter & _
                    " AND ([ArticleIssue] Like '" & _
                    Me!txtFindArticleIssue & "*')"
 
    'txtFindCreationDate - Date
    If Me!txtFindCreationDate > "" Then _
        strFilter = strFilter & _
                    " AND ([CreationDate]=" & _
                    Format(CDate(Me!txtFindCreationDate), _
                           "\#m/d/yyyy\#") & ")"
 
    'txtFindKeyword - Text
    ??????
 
    'cboFindDomain - Numeric
    If Me!cboFindDomain > "" Then _
        strFilter = strFilter & _
                    " AND ([Domain]=" & _
                    Me!cboFindDomain & ")"
 
    'Debug.Print ".Filter = '" & strOldFilter & "' - ";
    'Debug.Print "strFilter = '" & strFilter & " '"
    'Tidy up results and apply IF NECESSARY
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
End Sub
 

[andro007]

Based on my current code, it only manage to retrieve the first word.

For example, "Apple Banana Cat". If I search for Apple, the record will show. If I search for Cat, there will be no records shown.

Hence, can someone help me modify my code for 'txtFindKeyword - Text

[PhilOfWalton]

You need wild cards (*) both before and after the text you are searching for

 strFilter = strFilter & _
                    " AND ([ArticleIssue] Like '*" & _
                    Me!txtFindArticleIssue & "*')"
 

You have currently only got the * after the search string.

Phil

[andro007]

Omg, thanks. It works.

Now I have another question.

For example, I have 3 columns of category.
Is it possible to search for A.I for example, then Top1 and Top2 will be shown?

Or search for Social, then top2 and top3 will be shown?

My current code only let me search for only 1 category.

 If Me!cboFindDomain > "" Then _
        strFilter = strFilter & _
                    " AND ([Domain]=" & _
                    Me!cboFindDomain & ")"

Title | Category 1 | Category 2 | Category 3
---------------------------------------------------
Top1 | A.I | Drones |
---------------------------------------------------
Top2 | Social | A.I | Engineering
---------------------------------------------------
Top3 | Drones | Space | Social


Thanks in advance

[PhilOfWalton]

Everything is possible (hopefully)

I need details of your tables and what your form looks like.

Phil

[andro007]

Sure, I have attached my current file. Pls look into it and hopefully you can provide some tips or guide me along.

[PhilOfWalton]

Sorry andro007, sometimes it's necessary to break things before you fix them and I'm afraid that's just what I've done.

So a few pointers first. I loathe lookup tables, so have created a new table "TblDomains" and removed the lookups in FS_Database table.
I have set up the relationships between these tables.

I strongly advise against spaced in the names of all access objects (forms, queries, tables & reports) and in field names. If you have spaces, you have to surround the field names with square brackets[]

The Combo boxes now show meaningful information.

Also as I am getting on a bit, I can't read 8 point text. I have put it up to 10 point, but personally, providing the form doesn't get too big, I use 12 point.

So now we can get down to your problems. When you are searching for a domain, are you looking for it in any of the 3 domain fields, or specifically in domain1, domain2 or domain3.

Your coding is quite good, but we will modify that when I have answers to the above

Phil

[andro007]

When I am searching for a domain, I am trying to retrieve information from either of the 3 domain fields. As long either of the 3 domain fields contain "Social Media" for example, the record will be shown.


By the way, thanks for your time, appreciated it.

I used to do web programming and python. It's my first time touching on access and vba, so I felt a little lost. I will look at your file and try to understand more.

Cheers! :)

[PhilOfWalton]

Try this

I have replaced the CheckFilter with CreateFilter so you can delete the former

Phil

[andro007]

Yup, that's what I wanted. It is working great.

Thanks Phil.