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: - Private Sub cmdSearch_Click()
-
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
-
MsgBox "You must select a field to search."
-
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
-
MsgBox "You must enter a search string."
-
Else
-
'Generate search criteria
-
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
-
-
'Filter frmReferenceBooks based on search criteria
-
Form_frmReferenceBooks.RecordSource = "select * from tblReferenceBooks where " & GCriteria
-
Form_frmReferenceBooks.Caption = "tblReferenceBooks (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
-
-
MsgBox "Results have been filtered."
-
End If
-
End Sub
Thanks!
~mforema
5 11947 NeoPa 32,568
Recognized Expert Moderator MVP
Try something like : - Private Sub cmdSearch_Click()
-
Dim intIdx As Integer
-
Dim strWork As String, strSearch As String
-
Dim GCriteria As String 'Unnecessary if Dimensioned elsewhere
-
-
If Nz(cboSearchField, "") = "" Then
-
MsgBox "You must select a field to search."
-
ElseIf Nz(txtSearchString, "") = "" Then
-
MsgBox "You must enter a search string."
-
Else
-
'Generate search criteria
-
GCriteria = ""
-
strSearch = txtSearchString
-
Do
-
intIdx = InStr(1, strSearch, ",")
-
If intIdx > 0 Then
-
strWork = Trim(Left(strSearch, intIdx - 1)
-
strSearch = Mid(strSearch, intIdx + 1)
-
Else
-
strWork = Trim(strSearch)
-
strSearch = ""
-
End If
-
GCriteria = GCriteria & " AND ([" & cboSearchField & _
-
"] Like '*" & strWork & "*')"
-
Loop While strSearch > ""
-
GCriteria = Mid(GCriteria, 6)
-
With Me 'Assuming Me = Form_frmReferenceBooks otherwise With Form_frmReferenceBooks
-
'Filter frmReferenceBooks based on search criteria
-
.RecordSource = "SELECT * " & _
-
"FROM tblReferenceBooks " & _
-
"WHERE " & GCriteria
-
.Caption = "tblReferenceBooks (" & _
-
cboSearchField & _
-
" contains '" & _
-
txtSearchString & "')"
-
End With
-
MsgBox "Results have been filtered."
-
End If
-
End Sub
NeoPa 32,568
Recognized Expert Moderator MVP
NB. I removed the wildcards (*) from the .Caption, as that would not read very intelligently for multiple keywords.
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: - Private Sub cmdSearch_Click()
-
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
-
MsgBox "You must select a field to search."
-
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
-
MsgBox "You must enter a search string."
-
Else
-
'Generate search criteria
-
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
-
-
'Filter frmReferenceBooks based on search criteria
-
Form_frmReferenceBooks.RecordSource = "select * from tblReferenceBooks where " & GCriteria
-
Form_frmReferenceBooks.Caption = "tblReferenceBooks (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
-
-
MsgBox "Results have been filtered."
-
End If
-
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. -
Public Function KeyWordsInStr(ByVal strKeyWords As String, _
-
ByVal varField As Variant) As Boolean
-
Dim intPos As Integer
-
Dim strKeyWord As String
-
-
KeyWordsInStr = False
-
-
Do
-
intPos = InStr(1, strKeyWords, ",")
-
If intPos = 0 Then
-
strKeyWord = RTrim(strKeyWords)
-
Else
-
strKeyWord = Left(strKeyWords, intPos - 1)
-
strKeyWords = LTrim(Mid(strKeyWords, intPos + 1))
-
End If
-
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
-
Loop Until intPos = 0
-
-
KeyWordsInStr = True
-
End Function
-
Try something like : - Private Sub cmdSearch_Click()
-
Dim intIdx As Integer
-
Dim strWork As String, strSearch As String
-
Dim GCriteria As String 'Unnecessary if Dimensioned elsewhere
-
-
If Nz(cboSearchField, "") = "" Then
-
MsgBox "You must select a field to search."
-
ElseIf Nz(txtSearchString, "") = "" Then
-
MsgBox "You must enter a search string."
-
Else
-
'Generate search criteria
-
GCriteria = ""
-
strSearch = txtSearchString
-
Do
-
intIdx = InStr(1, strSearch, ",")
-
If intIdx > 0 Then
-
strWork = Trim(Left(strSearch, intIdx - 1)
-
strSearch = Mid(strSearch, intIdx + 1)
-
Else
-
strWork = Trim(strSearch)
-
strSearch = ""
-
End If
-
GCriteria = GCriteria & " AND ([" & cboSearchField & _
-
"] Like '*" & strWork & "*')"
-
Loop While strSearch > ""
-
GCriteria = Mid(GCriteria, 6)
-
With Me 'Assuming Me = Form_frmReferenceBooks otherwise With Form_frmReferenceBooks
-
'Filter frmReferenceBooks based on search criteria
-
.RecordSource = "SELECT * " & _
-
"FROM tblReferenceBooks " & _
-
"WHERE " & GCriteria
-
.Caption = "tblReferenceBooks (" & _
-
cboSearchField & _
-
" contains '" & _
-
txtSearchString & "')"
-
End With
-
MsgBox "Results have been filtered."
-
End If
-
End Sub
Thanks! It worked perfectly!
NeoPa 32,568
Recognized Expert Moderator MVP
A pleasure, and thanks for letting us know :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
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
|
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
|
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) ||
|
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,
|
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...
| |
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...
|
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...
|
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.
|
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...
|
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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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...
|
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...
|
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,...
|
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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();...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |