473,686 Members | 3,347 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Search by Multiple Keywords

72 New Member
Hi Everyone,

I want to search records by typing in multiple keywords. I currently have a search form. It has a combo box, text box, Search command button, and a subform. The combo box lists the names of the fields found in my subform. The search form is supposed to allow a user to choose which field he/she wants to search by and then type a keyword(s) in the text box. The subform should display the filtered results.

My problem occurs when I try to use multiple keywords.

For instance, I have a field called KEYWORD in the combo box. In my table, the KEYWORD field could contain only one keyword (like "Distillati on") OR it could have multiple keywords (like "Distillati on, Crystalization, Vaporization") for each record. In other words, each record has either one keyword or many keywords.

I've been able to write code that searches by single keywords. I can also type in a string of keywords to get a result, BUT they have to be written in the same order exactly as they are found in the table. For instance, using my previous example, I could type in "Distillati on, Crystalization" and I would get a result. But if I type "Distillati on, Vaporization", I don't get any results b/c that string is not recognized.

How do I get around this problem?

My search code is found below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.     If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
  3.         MsgBox "You must select a field to search."
  4.     ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
  5.         MsgBox "You must enter a search string."
  6.     Else
  7.         'Generate search criteria
  8.         GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
  9.  
  10.         'Filter frmReferenceBooks based on search criteria
  11.         Form_frmReferenceBooks.RecordSource = "select * from tblReferenceBooks where " & GCriteria
  12.         Form_frmReferenceBooks.Caption = "tblReferenceBooks (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
  13.  
  14.         MsgBox "Results have been filtered."
  15.     End If
  16. End Sub  
Thanks!
~mforema
Jul 2 '07 #1
5 11960
NeoPa
32,569 Recognized Expert Moderator MVP
Try something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.     Dim intIdx As Integer
  3.     Dim strWork As String, strSearch As String
  4.     Dim GCriteria As String    'Unnecessary if Dimensioned elsewhere
  5.  
  6.     If Nz(cboSearchField, "") = "" Then
  7.         MsgBox "You must select a field to search."
  8.     ElseIf Nz(txtSearchString, "") = "" Then
  9.         MsgBox "You must enter a search string."
  10.     Else
  11.         'Generate search criteria
  12.         GCriteria = ""
  13.         strSearch = txtSearchString
  14.         Do
  15.             intIdx = InStr(1, strSearch, ",")
  16.             If intIdx > 0 Then
  17.                 strWork = Trim(Left(strSearch, intIdx - 1)
  18.                 strSearch = Mid(strSearch, intIdx + 1)
  19.             Else
  20.                 strWork = Trim(strSearch)
  21.                 strSearch = ""
  22.             End If
  23.             GCriteria = GCriteria & " AND ([" & cboSearchField & _
  24.                         "] Like '*" & strWork & "*')"
  25.         Loop While strSearch > ""
  26.         GCriteria = Mid(GCriteria, 6)
  27.         With Me   'Assuming Me = Form_frmReferenceBooks otherwise With Form_frmReferenceBooks
  28.             'Filter frmReferenceBooks based on search criteria
  29.             .RecordSource = "SELECT * " & _
  30.                             "FROM tblReferenceBooks " & _
  31.                             "WHERE " & GCriteria
  32.             .Caption = "tblReferenceBooks (" & _
  33.                        cboSearchField & _
  34.                        " contains '" & _
  35.                        txtSearchString & "')"
  36.         End With
  37.         MsgBox "Results have been filtered."
  38.     End If
  39. End Sub
Jul 2 '07 #2
NeoPa
32,569 Recognized Expert Moderator MVP
NB. I removed the wildcards (*) from the .Caption, as that would not read very intelligently for multiple keywords.
Jul 2 '07 #3
FishVal
2,653 Recognized Expert Specialist
Hi Everyone,

I want to search records by typing in multiple keywords. I currently have a search form. It has a combo box, text box, Search command button, and a subform. The combo box lists the names of the fields found in my subform. The search form is supposed to allow a user to choose which field he/she wants to search by and then type a keyword(s) in the text box. The subform should display the filtered results.

My problem occurs when I try to use multiple keywords.

For instance, I have a field called KEYWORD in the combo box. In my table, the KEYWORD field could contain only one keyword (like "Distillati on") OR it could have multiple keywords (like "Distillati on, Crystalization, Vaporization") for each record. In other words, each record has either one keyword or many keywords.

I've been able to write code that searches by single keywords. I can also type in a string of keywords to get a result, BUT they have to be written in the same order exactly as they are found in the table. For instance, using my previous example, I could type in "Distillati on, Crystalization" and I would get a result. But if I type "Distillati on, Vaporization", I don't get any results b/c that string is not recognized.

How do I get around this problem?

My search code is found below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
  3. MsgBox "You must select a field to search."
  4. ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
  5. MsgBox "You must enter a search string."
  6. Else
  7. 'Generate search criteria
  8. GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
  9.  
  10. 'Filter frmReferenceBooks based on search criteria
  11. Form_frmReferenceBooks.RecordSource = "select * from tblReferenceBooks where " & GCriteria
  12. Form_frmReferenceBooks.Caption = "tblReferenceBooks (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
  13.  
  14. MsgBox "Results have been filtered."
  15. End If
  16. End Sub 
Thanks!
~mforema
Hi!

As far as I've got it you want your search criteria evaluate to True if all keywords provided as comma delimited string (txtSearchStrin g) are found in text type field no matter in what order they appear.

There are at least two ways.

1. Split txtSearchString into separate keywords and generate criteria like
InStr(,[Field],keyword1)<>0 AND InStr(,[Field],keyword1)<>0 AND ... AND InStr(,[Field],keywordN)<>0

2. Write VBA function performing desired string comparisson to use it in SQL expression.

Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2. ByVal varField As Variant) As Boolean
  3. Dim intPos As Integer
  4. Dim strKeyWord As String
  5.  
  6. KeyWordsInStr = False
  7.  
  8. Do
  9. intPos = InStr(1, strKeyWords, ",")
  10. If intPos = 0 Then
  11. strKeyWord = RTrim(strKeyWords)
  12. Else
  13. strKeyWord = Left(strKeyWords, intPos - 1)
  14. strKeyWords = LTrim(Mid(strKeyWords, intPos + 1))
  15. End If
  16. If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
  17. Loop Until intPos = 0
  18.  
  19. KeyWordsInStr = True
  20. End Function
  21.  
Jul 2 '07 #4
mforema
72 New Member
Try something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.     Dim intIdx As Integer
  3.     Dim strWork As String, strSearch As String
  4.     Dim GCriteria As String    'Unnecessary if Dimensioned elsewhere
  5.  
  6.     If Nz(cboSearchField, "") = "" Then
  7.         MsgBox "You must select a field to search."
  8.     ElseIf Nz(txtSearchString, "") = "" Then
  9.         MsgBox "You must enter a search string."
  10.     Else
  11.         'Generate search criteria
  12.         GCriteria = ""
  13.         strSearch = txtSearchString
  14.         Do
  15.             intIdx = InStr(1, strSearch, ",")
  16.             If intIdx > 0 Then
  17.                 strWork = Trim(Left(strSearch, intIdx - 1)
  18.                 strSearch = Mid(strSearch, intIdx + 1)
  19.             Else
  20.                 strWork = Trim(strSearch)
  21.                 strSearch = ""
  22.             End If
  23.             GCriteria = GCriteria & " AND ([" & cboSearchField & _
  24.                         "] Like '*" & strWork & "*')"
  25.         Loop While strSearch > ""
  26.         GCriteria = Mid(GCriteria, 6)
  27.         With Me   'Assuming Me = Form_frmReferenceBooks otherwise With Form_frmReferenceBooks
  28.             'Filter frmReferenceBooks based on search criteria
  29.             .RecordSource = "SELECT * " & _
  30.                             "FROM tblReferenceBooks " & _
  31.                             "WHERE " & GCriteria
  32.             .Caption = "tblReferenceBooks (" & _
  33.                        cboSearchField & _
  34.                        " contains '" & _
  35.                        txtSearchString & "')"
  36.         End With
  37.         MsgBox "Results have been filtered."
  38.     End If
  39. End Sub
Thanks! It worked perfectly!
Jul 3 '07 #5
NeoPa
32,569 Recognized Expert Moderator MVP
A pleasure, and thanks for letting us know :)
Jul 3 '07 #6

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

Similar topics

3
1570
by: huzz | last post by:
I've a table filed holds keywords seperated by comma, and a web search form where a user can type one of multiple keywords in the search text field seperated by comma aswell. How do i create a sql statement to bring results that matches one or more keywords in the database? Many thanks in advance.. huzz
83
5926
by: D. Dante Lorenso | last post by:
Trying to use the 'search' in the docs section of PostgreSQL.org is extremely SLOW. Considering this is a website for a database and databases are supposed to be good for indexing content, I'd expect a much faster performance. I submitted my search over two minutes ago. I just finished this email to the list. The results have still not come back. I only searched for: SECURITY INVOKER
32
14847
by: tshad | last post by:
Can you do a search for more that one string in another string? Something like: someString.IndexOf("something1","something2","something3",0) or would you have to do something like: if ((someString.IndexOf("something1",0) >= 0) || ((someString.IndexOf("something2",0) >= 0) ||
5
4179
by: JP SIngh | last post by:
Hi All This is a complicated one, not for the faint hearted :) :) :) Please help if you can how to achieve this search. We have a freetext search entry box to allow users to search the database. I am searching two tables. SELECT TapeRecords.Id, TapeRecords.ItemTitle, TapeRecords.SourceRef,
3
1912
by: Richard S | last post by:
CODE: ASP.NET with C# DATABASE: ACCES alright, im having a problem, probably a small thing, but i cant figure out, nor find it in any other post, or on the internet realy (probably cuz i wouldnt know what to search for), but heres the problem: I am making a search function for my website, i want this to be possible: - search for 1 keyword (the problem guy) - search for multiple keywords things that alreaddy work (so no problem with...
2
2670
by: rlemusic | last post by:
Hi everybody, Iím creating a database in Access (I believe itís 2000) to catalogue items in the archives of a small museum. Iím a total n00b as far as using Access goes, but by looking at some online tutorials and how the museumís existing collections catalogue is set up in Access, Iíve been able to come up with a basic database that suits the museumís needs. My biggest issues right now concern Relationships and Codes. I managed to get a...
0
1309
by: Bob Alston | last post by:
I am doing volunteer work with a human services referral agency. The want me to build a database of referral data - other agencies, sources of information, etc. Ideally it would be a structured search searching different fields of the database, like cities served, predefined keywords, etc. Obviously I could build this. Using a Query by form type approach. but I am also thinking about the database portion. If there is a field in...
0
1759
by: Skywick | last post by:
Hi I am trying to do a full text search with a column name for the search term. I can do this using LIKE with: SELECT tblContent.ID FROM tblContent INNER JOIN #keywords ON tblContent.words LIKE '%' + #keywords.keyword + '%' But I can not seem to do this using the CONTAINS syntax. eg.
18
2404
by: luke noob | last post by:
can some one please help me adapt my script so that i can search for multiple words within my database, my keywords feild looks something like this.... Big Teddie Bear Collection Tiny Small Soft At the moment if i search for the word "teddie" i will get "teddie" and if i search for "teddie bear" i will get "teddie bear", because in the keywords feild in the database "teddie" and "bear" are next to each other. but if i search for...
0
8584
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9054
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8932
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8768
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8778
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7599
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectóplanning, coding, testing, and deploymentówithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5796
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4532
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2945
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.