Searching a string using VBA

I've got an Access97 database with a table called IssueTable that contains details of issues raised by people in my department. One of the fields in this table is called ShortDesc, which is where users have to enter a Short Description of the issue they're raising. What I want is to have a form with a search box (which I've named KeywordSearch) so that people can enter a keyword(s) into the box and which will then search and return a list of all the records where the keyword(s) exist in the Short Description. I'm sure there must be a way of using SQL or even a bit of VBA which will search through the records in the IssueTable and pick out the ones which match the search criteria based on the keywords entered.

Here is one way you could do it. It's kinda hard to fully explain without writing an epic (some explanation follows the code sample) so I've attatched a tiny example database that has a small IssueTable, the query that gets modified, and a keyword search form. If you check out and run the example form do some searches on the keywords 'paul', 'computer', 'hd', and/or 'problem' to see it work.

The heart of the code is behind the search button:
<PRE>Private Sub cmdSearch_Click()
Dim strCombinedKeywords As String
Dim strKeywords() As String
Dim intIndex As Integer
Dim strSQL As String
Dim qdfSearch As QueryDef

' Get the group of keywords.
strCombinedKeywords = Trim$(txtKeywords.Value)

' Split the keywords into an array.
Do While InStr(strCombinedKeywords, " ") > 0
ReDim Preserve strKeywords(0 To intIndex) As String
strKeywords(intIndex) = Mid$(strCombinedKeywords, 1, InStr(strCombinedKeywords, " ") - 1)
strCombinedKeywords = Mid$(strCombinedKeywords, InStr(strCombinedKeywords, " ") + 1)
strCombinedKeywords = Trim$(strCombinedKeywords) ' Trim off any extra spaces put in by the user.
intIndex = intIndex + 1
Loop
ReDim Preserve strKeywords(0 To intIndex)
strKeywords(intIndex) = strCombinedKeywords

' Build up an SQL SELECT statement.
strSQL = "SELECT * FROM IssueTable WHERE "
For intIndex = 0 To UBound(strKeywords)
strSQL = strSQL & "[ShortDesc] LIKE '*" & strKeywords(intIndex) & "*'"
If Not intIndex = UBound(strKeywords) Then
If fraSearchLogic.Value = 1 Then
strSQL = strSQL & " AND "
Else
strSQL = strSQL & " OR "
End If
End If
Next intIndex

' Redefine the results query.
Set qdfSearch = CurrentDb.QueryDefs("IssueTableSearch")
qdfSearch.SQL = strSQL
qdfSearch.Close
Set qdfSearch = Nothing
DoEvents
' Display the results query.
DoCmd.OpenQuery "IssueTableSearch", acViewNormal, acReadOnly

End Sub</PRE>What's happening is that the keywords are broken down into individual words and placed in a dynamic array. The words are then used to build up an SQL statement that's using the LIKE operator to check for the existence of the word within a field (SmallDesc). In this example the user can select AND or OR search logic for more robust searching - so the ANDs or ORs are built in to the SQL statement depending on the user selection (option controls within a frame control). Once the SQL statement is built the search results query is altered using a QueryDef object. After the QueryDef has been redefined it is displayed using DoCmd.OpenQuery.

There are a variety of ways you could deal with your search results once you have the SQL statement. You could do something with a recordset, fill a list control with the SQL statement, or whatever. I used the QueryDef thing in this example because it was a quick way to display the search results.

I hope this helps or inspires,
Paul

Hi,

I have looked at the database you have attached and it comes up with an error message saying "Can't Find Project or Library".

I am very interested in this code since i have been working on a very similar project.

thx

Adi