multiple keyword search to display to data grid control

[Will Storer]

I am currently working on a project that involves using a multiple keyword
search of an access database. The search result is supposed to display to
a data grid. Right now, the code that i have will:

1. Accept the mulitiple keywords from a text box
2. Separate the keywords into an array
3. search each keyword in with an sql statement through a loop

When it comes to the output, the data grid only displays the result of the
last word in the the keyword search text box.

Here is my code:

**************************************************************
Private Sub cmdKeyWordSearch_Click()
Dim strKeyWord As String
Dim SplitKeyWord() As String
Dim x As Integer
x = 0

strKeyWord = " " + txtKeyWord.Text
SplitKeyWord = Split(strKeyWord)

Do Until x = UBound(SplitKeyWord) + 1

SplitKeyWord(x) = "%" & SplitKeyWord(x) & "%"
adoClients.RecordSource = "SELECT * FROM ClientAddresses " & _
"WHERE Company LIKE" & Chr(34) & SplitKeyWord(x) & Chr(34)
adoClients.Refresh
dgrdClients.Visible = True
x = x + 1
loop

End Sub
**************************************************************
I need the data grid to display all the addresses where the company has any
of the keywords in it.

Thank you in advance for the response :-)

[Colin McGuigan]

Will Storer wrote in message <38fe0722@news.devx.com>...
>
>I am currently working on a project that involves using a multiple keyword
>search of an access database. The search result is supposed to display to
>a data grid. Right now, the code that i have will:
>
>1. Accept the mulitiple keywords from a text box
>2. Separate the keywords into an array
>3. search each keyword in with an sql statement through a loop
>
>When it comes to the output, the data grid only displays the result of the
>last word in the the keyword search text box.
>
>Here is my code:
>
>**************************************************************
>Private Sub cmdKeyWordSearch_Click()
> Dim strKeyWord As String
> Dim SplitKeyWord() As String
> Dim x As Integer
> x = 0
>
> strKeyWord = " " + txtKeyWord.Text
> SplitKeyWord = Split(strKeyWord)
>
> Do Until x = UBound(SplitKeyWord) + 1
>
> SplitKeyWord(x) = "%" & SplitKeyWord(x) & "%"
> adoClients.RecordSource = "SELECT * FROM ClientAddresses " & _
> "WHERE Company LIKE" & Chr(34) & SplitKeyWord(x) & Chr(34)
> adoClients.Refresh
> dgrdClients.Visible = True
> x = x + 1
> loop
>
>End Sub
>**************************************************************
>I need the data grid to display all the addresses where the company has any
>of the keywords in it.
>
>Thank you in advance for the response :-)
>
>

What database are you using? If you're using SQL Server, you can do
something like this by using the 'UNION' keyword:

<warning: Vaporcode>

Private Sub cmdKeyWordSearch_Click()
Dim strKeyWord As String
Dim strSQL As String
Dim SplitKeyWord() As String
Dim x As Integer

strKeyWord = " " + txtKeyWord.Text
SplitKeyWord = Split(strKeyWord)

For x = LBound(SplitKeyWord) To UBound(SplitKeyWord)
strSQL = strSQL & "SELECT * FROM ClientAddresses " & _
"WHERE Company LIKE '%" & SplitKeyWord(x) & "%'"
If x < UBound(SplitKeyWord) Then
strSQL = strSQL & " UNION "
End If
Next x
adoClients.RecordSource = strSQL
adoClients.Refresh
dgrdClients.Visible = True
End Sub

What this should do is produce a SQL statement like this:

SELECT * FROM ClientAddresses WHERE Company LIKE '%foobar%'
UNION
SELECT * FROM ClientAddresses WHERE Company LIKE '%barfoo%'
UNION
SELECT * FROM ClientAddresses...etc

However, a more efficient way to do this might be to append OR statements
onto your SQL, so the command ends up looking like:

SELECT * FROM ClientAddresses
WHERE Company LIKE '%foobar%'
OR Company LIKE '%barfoo%'
OR Company LIKE...etc

--
Colin McGuigan