Multiple Keyword Search

[Vassago]

This is my first post here so bear with me.

What I am trying to accomplish is a mulitple search engine for records on one table in my database.

Basically what I want to accomplish is what user listo accomplished here http://www.access-programmers.co.uk/forums/showthread.php?threadid=37162&referrerid=11730 but didn't explain how.

I will have a form with a text box, if the user inputs "Macromedia Flash" and hits the search button, I was all records with the words "macromedia" or "flash" to be in the search results.

example results would be:

Macromedia Flash MX
Macromedia Dreamweaver MX
Flash maker v3.5

all because they contain at least one of those two words.

He basically had the code I want, but didn't supply it in that post, as he was asking a question.

Please help.

Vassago

 

[Jon K]

An example is contained in the attached DB.

Open the form. Enter some keywords (e.g. Macromedia Flash) in the text box and click on the Search button. A query based on the input will be created and run. (If no records are to be returned, a message will be displayed instead.)

The code is in the On Click event of the command button:-
-----------------------------
Private Sub Command0_Click()
' exit if nothing is entered on the form.
If IsNull(Me.txtKeywords) Then Exit Sub

' declare variables.
Dim db As DAO.Database
Dim qDef As DAO.QueryDef

Dim Keywords As String
Dim fieldToSearch As String
Dim Criteria As String
Dim SQL As String

' remove leading and trailing spaces, if any, from user input.
Keywords = Trim(Me.txtKeywords)

' specify which field to search.
fieldToSearch = "[Software]"

' create criteria based on input.
Do While InStr(Keywords, " ") > 0
Criteria = Criteria & fieldToSearch & " Like ""*" & Left(Keywords, InStr(Keywords, " ") - 1) & "*"" or "
Keywords = Mid(Keywords, InStr(Keywords, " ") + 1)
Loop
Criteria = Criteria & fieldToSearch & " Like ""*" & Keywords & "*"""

' build SQL statement.
SQL = "SELECT RecordID, Software" & _
" FROM tblSoftware" & _
" WHERE " & Criteria

Set db = CurrentDb

' delete query qryTempSearch if already exists.
On Error Resume Next
db.QueryDefs.Delete "qryTempSearch"
On Error GoTo 0

' create query gryTempSearch.
db.CreateQueryDef "qryTempSearch", SQL

' if there are records to be returned, run the query,
' otherwise display a message.
If DCount("*", "qryTempSearch") > 0 Then
DoCmd.OpenQuery "qryTempSearch"
Else
MsgBox "No such records in table tblSoftware"
End If

Set db = Nothing
End Sub
----------------------------------------

Note. The code was written in Access 97, so DAO was used. If you write the code in Access 2000 or XP, you must make a reference to DAO (when the code window is open, choose menu Tools, References... and select the Microsoft DAO 3.6 Object Library from the Available References list.)

Hope it helps.


Edit:-

The Keywords string in the Do While...Loop should be left trimmed in case more than one spaces are entered between the key words:-

Keywords = LTrim(Mid(Keywords, InStr(Keywords, " ") + 1))

 

[Vassago]

That's a perfect one, thank you so much.