Search Form Using Keywords

[rustyCrVx]

Access 2003

Hopefully I can word this properly to make sense.

I currently have a main search form with unbound controls (search criteria) in the header with an unbound subform in the details section. I have a command button on the main form that builds the filter criteria according to what's been filled out in the search criteria. It all works rather simply except I'm stuck on one part. There is a text box to search through a memo field that I want to return all records containing any words entered into the text box.

In other words if a user enters <payroll invoices "attorney meeting">, records containing "payroll", "invoices", and/or "attorney meeting" will show in the subform. Basically each word separated by a space is searched for unless the user enters a phrase within quotes.

This is the way I made one of the other searches for our clients but I'm not sure this method can be used with what I need this other search to do:

Private Sub cmdFilter_Click()
    Dim strWhere As String
    Dim lngLen As Long
 
    '*******************************************************
    'Look at each search box and create the filter criteria*
    '*******************************************************
 
    If Not IsNull(Me.txtFilterFileNo) Then
        strWhere = strWhere & "([FileNo] Like ""*" & Me.txtFilterFileNo & "*"") AND "
    End If
 
    If Not IsNull(Me.txtFilterName) Then
        strWhere = strWhere & "([Client Name] Like ""*" & Me.txtFilterName & "*"") AND "
    End If
 
'etc.
 
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        If MsgBox("List all records?", vbYesNo, "No Criteria") = vbYes Then
            Me!subfClientMasterSearch.Visible = True
            Me.subfClientMasterSearch.Form.FilterOn = False
        Else
            Exit Sub
        End If
    Else
        Me!subfClientMasterSearch.Visible = True
        strWhere = Left$(strWhere, lngLen)
        Me!subfClientMasterSearch.Form.FilterOn = True
        Me!subfClientMasterSearch.Form.Filter = strWhere
    End If
 
End Sub

Anyways, I'd appreciate any help/examples/links.

 

[dkinley]

Didn't really read your post ... just posting a link to an example.

http://www.techonthenet.com/access/forms/search4.php

-dk

 

[rustyCrVx]

Thanks for the response but it didn't help much

I already have mine set up working pretty much the same way the example did but what I'm looking for didn't work in the example. I need a way to possibly enter more than one word in a search field, then have the subform show any results with word1 displayed, any with word2 displayed, any with word3 displayed or any with a phrase displayed if the search is enclosed in quotes. A bit like how the Forums search functions where it splits up the individual words entered and retrieves any posts containing those words. Make sense?

 

[rustyCrVx]

*bump*

Still looking for help on this

Anybody?