By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Search a for a specific keyword in the summary

P: 6
Hi guys,

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

This is my code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtFindArticleIssue_AfterUpdate()
  2.     Call CheckFilter
  3. End Sub
  4.  
  5. Private Sub txtFindKeyword_AfterUpdate()
  6.     Call CheckFilter
  7. End Sub
  8.  
  9. Private Sub txtFindCreationDate_AfterUpdate()
  10.     Me!txtFindCreationDate = IIf(IsDate(Me!txtFindCreationDate), _
  11.                                  Format(Me!txtFindCreationDate, "d mmm yyyy"), _
  12.                                  "")
  13.     Call CheckFilter
  14. End Sub
  15.  
  16. Private Sub cboFindDomain_AfterUpdate()
  17.     Call CheckFilter
  18. End Sub
  19.  
  20. 'CheckFilter produces the new Filter depending on the values currently in
  21. 'txtFindArticleIssue, txtFindCreationDate, txtFindKeyword & cboFindDomain.
  22. Private Sub CheckFilter()
  23.     Dim strFilter As String, strOldFilter As String
  24.     strOldFilter = Me.Filter
  25.  
  26.     'txtFindArticleIssue - Text
  27.     If Me!txtFindArticleIssue > "" Then _
  28.         strFilter = strFilter & _
  29.                     " AND ([ArticleIssue] Like '" & _
  30.                     Me!txtFindArticleIssue & "*')"
  31.  
  32.     'txtFindCreationDate - Date
  33.     If Me!txtFindCreationDate > "" Then _
  34.         strFilter = strFilter & _
  35.                     " AND ([CreationDate]=" & _
  36.                     Format(CDate(Me!txtFindCreationDate), _
  37.                            "\#m/d/yyyy\#") & ")"
  38.  
  39.     'txtFindKeyword - Text
  40.     ??????
  41.  
  42.     'cboFindDomain - Numeric
  43.     If Me!cboFindDomain > "" Then _
  44.         strFilter = strFilter & _
  45.                     " AND ([Domain]=" & _
  46.                     Me!cboFindDomain & ")"
  47.  
  48.     'Debug.Print ".Filter = '" & strOldFilter & "' - ";
  49.     'Debug.Print "strFilter = '" & strFilter & " '"
  50.     'Tidy up results and apply IF NECESSARY
  51.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  52.     If strFilter <> strOldFilter Then
  53.         Me.Filter = strFilter
  54.         Me.FilterOn = (strFilter > "")
  55.     End If
  56. End Sub
  57.  
Mar 3 '17 #1

✓ answered by PhilOfWalton

Try this

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

Phil

Share this Question
Share on Google+
9 Replies


P: 6
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
Mar 3 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
You need wild cards (*) both before and after the text you are searching for

Expand|Select|Wrap|Line Numbers
  1.  strFilter = strFilter & _
  2.                     " AND ([ArticleIssue] Like '*" & _
  3.                     Me!txtFindArticleIssue & "*')"
  4.  
You have currently only got the * after the search string.

Phil
Mar 3 '17 #3

P: 6
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.
Expand|Select|Wrap|Line Numbers
  1.  If Me!cboFindDomain > "" Then _
  2.         strFilter = strFilter & _
  3.                     " AND ([Domain]=" & _
  4.                     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
Mar 3 '17 #4

PhilOfWalton
Expert 100+
P: 1,430
Everything is possible (hopefully)

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

Phil
Mar 3 '17 #5

P: 6
Sure, I have attached my current file. Pls look into it and hopefully you can provide some tips or guide me along.
Attached Files
File Type: zip FSTable.zip (52.5 KB, 24 views)
Mar 3 '17 #6

PhilOfWalton
Expert 100+
P: 1,430
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
Mar 3 '17 #7

P: 6
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! :)
Mar 3 '17 #8

PhilOfWalton
Expert 100+
P: 1,430
Try this

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

Phil
Mar 3 '17 #9

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

Thanks Phil.
Mar 4 '17 #10

Post your reply

Sign in to post your reply or Sign up for a free account.