How to search keywords on Access? (1 Viewer)

[shawnntjr]

Hi all,

I managed to create a form that is able to search keywords on Access 2007. However, the keyword search must be exact to what is contained in the table.

For example, I stored "red blue yellow" into the database, and I can only search "red blue yellow", "red blue" or "blue yellow" in order to call that data.

How do I make it such that I am able to search "blue red yellow", "yellow blue red" or "red yellow blue"?

These are my current codes:

Private Sub txtSearch_AfterUpdate()
    Dim strWhere As String
    Dim strWord As String
    Dim varKeywords As Variant
    Dim i As Integer
    Dim IngLen As Long
 
    'If Me.Dirty Then
        'Me.Dirty = False
    'End If
    If IsNull(Me.txtSearch) Then
        If Me.FilterOn Then
            Me.FilterOn = False
        End If
    Else
        varKeywords = Split(Me.txtSearch, " ")
        If UBound(varKeywords) >= 99 Then
            MsgBox "Too many words."
        Else
            For i = LBound(varKeywords) To UBound(varKeywords)
            strWord = Trim$(varKeywords(i))
            If strWord <> vbNullString Then
                strWhere = strWhere & "Directory.FName Like ""*" & strWord & "*"" AND "
            End If
            Next
            IngLen = Len(strWhere) - 4
            If IngLen > 0 Then
                Me.Filter = Left(strWhere, IngLen)
                Me.FilterOn = True
            Else
                Me.FilterOn = False
            End If
        End If
    End If
End Sub

Private Sub Command2_Click()
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim filepath As String
    Dim strSearch As String
    Dim rst As DAO.Recordset
    If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
        MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
        Me.txtSearch.BackColor = vbYellow
        Me.txtSearch.SetFocus
    Else
        strSearch = Me.txtSearch
        Set rst = CurrentDb.OpenRecordset("SELECT Directory.Directory FROM Directory WHERE ((Directory.FName Like ""*" & strSearch & "*""))")
        If rst.EOF Then
            MsgBox "File does not exist."
        Else
            Me.txtSearch.BackColor = vbWhite
            Set wrdApp = CreateObject("Word.Application")
            wrdApp.Visible = True
            filepath = rst![Directory]
            Set wrdDoc = wrdApp.Documents.Open(filepath)
        End If
    End If
End Sub

 

[CJ_London]

It looks to me like you are creating your search string in the txtsearch after update event to a local variable (strwhere) which is not avaibale outside the sub but then assigning txtsearch value to the where clause in your query. To resolve, make strwhere a public variable at the top of your form code module and then assign it to strsearch rather than me.txtsearch.

Without testing, it also looks like you will have too many asterisks when you resolve the above and your AND should be an OR

Other things to be aware of - you have a field called directory in a table called directory - this may cause problems

 

[shawnntjr]

It looks to me like you are creating your search string in the txtsearch after update event to a local variable (strwhere) which is not avaibale outside the sub but then assigning txtsearch value to the where clause in your query. To resolve, make strwhere a public variable at the top of your form code module and then assign it to strsearch rather than me.txtsearch.

Without testing, it also looks like you will have too many asterisks when you resolve the above and your AND should be an OR

Hi CJ, Thanks for the swift reply.

I did what you mentioned, however, now my search only opens the first row of my table no matter what I search. Am I just supposed to do these:

Public strWhere As String

strSearch = strWhere
        Set rst = CurrentDb.OpenRecordset("SELECT Directory.Directory FROM Directory WHERE ((Directory.FName Like ""*" & strSearch & "*""))")

As for the asterisk part, I am not exactly sure what you mean as my VBA knowledge isn't very strong.

 

[CJ_London]

about to go away so just very quickly

As for the asterisk part,

you have

strWhere = strWhere & "Directory.FName Like ""*" & strWord & "*"" AND "

and

....FROM Directory WHERE ((Directory.FName Like ""*" & strSearch & "*""))")

put these together (let's say strWord=Hello) and you have

....FROM Directory WHERE ((Directory.FName Like ""*Directory.FName Like ""*" HEllo "*"" "*""))")

try

....FROM Directory WHERE " & strWhere

 

[shawnntjr]

Well, it didn't really work, but thanks for your help anyway

 

[smig]

Try splitting both your KeyWords and SearchWords into array.
Now you need to search for any word in the SearchWords array into the KeyWords array.
You will need a counter to make sure all SearchWords where found.

Also think what you want to do in case your KeyWords looks like "Yellow Red Blue Red Blue"
Or in case your SearchWords looks like "Yellow Blue" and your KeyWords looks like "Yellow Red Blue"
But first solve the simple option

 

[stopher]

I tested your afterUpdate code and it worked fine i.e. it set the filter to list records with all the colours entered regardless of the order they where typed.

As for the onclick code, that is only searching using the exact string because that is what you've coded it to do. You could use the me.Filter instead of txtSearch because that's what you've coded in the afterUpdate event and it seems to work.

hth