Search capabillity

[GearHead]

Here is my problem:

I am trying to provide the ability to perform a search of records. The basic setup is as follows:

MS Access 2003
Visual Basic 6.3

Form as single form : frmAcftEditor
Subform as continuous form: subFaultViewer
Search is initiated with cmdSearchFaults command button and is located on header of subFaultViewer

Desired result is to enter search criteria and display all records containing criteria.

Here is the code I am attempting, I have tried other variations as well with no success. I am fairly new to VBA and not well versed as of yet. Hope someone can help and thanks in advance!

Private Sub cmdSearchFaults_Click()
 
Dim strDescription As String
Dim strCriteria As String
Dim strFilter As String
Dim strQuote As String
 
strQuote = Chr(34)
''' FaultDescription is a text field with 255 chr max length
strDescription = Me.FaultDescription
strCriteria = InputBox("Enter search word.")
''' search evaluates FaultDescription to see if it contains strCriteria
''' if so it returns the contents of fault description thus evaluating to true
''' if not it returns a zero lenth string "" evaluating to false
''' however it does not seem as though it evaluates the function for each record as the filter is applied
strFilter = strQuote & "[FaultDescription] = " & "'" & Search(strDescription, strCriteria) & "'" & strQuote
 
Me.Filter = strFilter
Me.FilterOn = True
    
End Sub
 
 
 
Public Function Search(Text, Word) As String
 
If InStr(1, Text, Word, 1) > 0 Then
    Search = Text
Else: Search = ""
End If
 
End Function

[si_the_geek]

Welcome to VBForums!

I could be wrong here (I don't use .Filter), but aren't the strQuote's unnescessary?

Also, what is the purpose of comparing in the Search function? The Filter should be doing all the work for you.

Try this instead:

Private Sub cmdSearchFaults_Click()
 
Dim strCriteria As String
Dim strFilter As String
 
strCriteria = InputBox("Enter search word.")
strFilter = "[FaultDescription] = '" & strCriteria & "'"
 
Me.Filter = strFilter
Me.FilterOn = True
 
End Sub

[GearHead]

Thanks. .Filter only looks for exact matches. I need to find if the field contains the search criteria not if it is a match to it. i.e. the field may contain several entries containing 5 to 20 words and I want to see only those records containing the word "cracked".

[si_the_geek]

Your previous code method would unfortunately not work, as you were only changing the text to search for in the filter - the same work would still be done (or the text would be cleared, in which case only records with a blank FaultDescription would be returned).

I don't know if Filter supports Like matching (partial text match), but if it does you can use one of these (probably the first):

strFilter = "[FaultDescription] Like '*" & strCriteria & "*'"
strFilter = "[FaultDescription] Like '%" & strCriteria & "%'"