NeuroAgent

VB6 ADO Filter: Search Words in Order with LIKE

Learn how to search for records where two words appear in order using VB6's ADODB.Recordset.Filter property. Discover workarounds for LIKE operator limitations and implement effective text filtering solutions.

#ado-database#database-querying#like-operator#recordset-filtering#text-search#vb6-programming
Question

How can I search for records where two specific words appear in order using the LIKE operator in VB6’s ADODB.Recordset.Filter property?

I’m working with an ADODB.Recordset called rsCustomers that persists in memory during my form’s lifecycle. I’m using the Filter property to search through records as the user types in a TextBox. The current approach using LIKE %John Doe% works when “John” is immediately followed by “Doe”, but it fails when there are other characters between them (like “John O’ Doe”).

I’ve tried using the syntax rsCustomers.Filter("Name LIKE %John%Doe%") similar to SQL Server, but it doesn’t seem to work. Is there a different syntax for this in VB6? Is this functionality even possible with the Filter property, or do I need to implement a different approach?

NeuroAgent

The ADODB.Recordset.Filter property in VB6 doesn’t support the same complex pattern matching as SQL Server. The LIKE %John%Doe% syntax you tried isn’t valid for ADO Filter, which only supports basic wildcard patterns at the beginning and/or end of strings. For searching records where two words appear in order with any characters between them, you’ll need to use a different approach.

Contents

Understanding ADO Filter Limitations

The ADO Filter property has specific constraints with the LIKE operator:

  • Wildcard Restrictions: The LIKE operator only supports * or % wildcards at the beginning and/or end of strings, not in the middle
  • Syntax Requirement: As stated in Microsoft documentation, the Filter property only supports basic pattern matching
  • String Formatting: Values must be enclosed in single quotes when using string comparisons

According to DevGuru, “if the Filter begins with a wildcard (* or %) then it must end with a wildcard (* or %)” - this explains why LIKE %John%Doe% fails, as it has wildcards in both middle and end positions.

Alternative Approaches

Since the Filter property can’t handle your specific requirement, consider these alternatives:

1. Use SQL Query with Regular Expressions

Many databases support regular expressions or pattern matching functions that can handle “words in order with characters between” scenarios.

2. Client-Side Filtering

Loop through the recordset and apply custom logic to check for the pattern.

3. Full-Text Search

If available, use database-specific full-text search capabilities.

Implementation Strategies

SQL-Based Solution

Modify your SQL query to use database-specific pattern matching:

vb
' For SQL Server using PATINDEX
strSQL = "SELECT * FROM Customers " & _
         "WHERE PATINDEX('%John% Doe%', Name) > 0"

' For Access using Instr
strSQL = "SELECT * FROM Customers " & _
         "WHERE InStr(1, [Name], 'John') > 0 AND " & _
         "InStr(InStr(1, [Name], 'John') + 4, [Name], 'Doe') > 0"

Client-Side Solution

If you must use the in-memory recordset, implement custom filtering:

vb
Function FilterByWordOrder(rs As ADODB.Recordset, fieldName As String, _
                          firstWord As String, secondWord As String) As Boolean
    Dim firstPos As Long, secondPos As Long
    
    firstPos = InStr(1, rs(fieldName), firstWord)
    If firstPos = 0 Then Exit Function
    
    secondPos = InStr(firstPos + Len(firstWord), rs(fieldName), secondWord)
    FilterByWordOrder = (secondPos > 0)
End Function

' Usage:
Dim filteredRS As New ADODB.Recordset
filteredRS.Filter = adFilterNone ' Clear any existing filter

rsCustomers.Filter = adFilterNone
rsCustomers.MoveFirst

Do While Not rsCustomers.EOF
    If FilterByWordOrder(rsCustomers, "Name", "John", "Doe") Then
        filteredRS.AddNew
        ' Copy all fields from rsCustomers to filteredRS
        ' (implementation depends on your field structure)
        filteredRS.Update
    End If
    rsCustomers.MoveNext
Loop

The most efficient approach is to modify your SQL query to use the database’s pattern matching capabilities. Here’s a comprehensive solution:

vb
Private Sub SearchCustomers(searchText As String)
    Dim firstWord As String, secondWord As String
    Dim strSQL As String
    Dim rsFiltered As New ADODB.Recordset
    
    ' Parse the search text to extract words
    ' This is a simplified example - you may need more sophisticated parsing
    Dim words() As String
    words = Split(searchText, " ")
    
    If UBound(words) >= 1 Then
        firstWord = words(0)
        secondWord = words(1)
        
        ' Build SQL based on database type
        Select Case GetDatabaseType()
            Case "SQLServer"
                strSQL = "SELECT * FROM Customers " & _
                         "WHERE PATINDEX('%" & firstWord & "% " & secondWord & "%', Name) > 0"
            Case "Access"
                strSQL = "SELECT * FROM Customers " & _
                         "WHERE InStr(1, [Name], '" & firstWord & "') > 0 AND " & _
                         "InStr(InStr(1, [Name], '" & firstWord & "') + 4, [Name], '" & secondWord & "') > 0"
            Case Else
                ' Default to simple containment
                strSQL = "SELECT * FROM Customers " & _
                         "WHERE Name LIKE '%" & searchText & "%'"
        End Select
    Else
        ' Single word search
        strSQL = "SELECT * FROM Customers WHERE Name LIKE '%" & searchText & "%'"
    End If
    
    ' Execute the query and get filtered results
    rsFiltered.Open strSQL, gConnection, adOpenStatic, adLockReadOnly
    
    ' Replace the current recordset with filtered results
    Set rsCustomers = rsFiltered
    rsCustomers.MoveFirst
    
    ' Update your UI to reflect the filtered results
    RefreshCustomerDisplay
End Sub

Private Function GetDatabaseType() As String
    ' Determine your database type - implement based on your connection
    ' This is a placeholder - you need to implement actual detection
    GetDatabaseType = "Access" ' or "SQLServer" etc.
End Function

Examples and Code

Here are practical examples for different scenarios:

Basic Two-Word Search

vb
' Search for "John" followed by "Doe" with any characters between
Private Sub SearchForJohnDoe()
    Dim strSQL As String
    
    ' For Access databases
    strSQL = "SELECT * FROM Customers " & _
             "WHERE InStr(1, [Name], 'John') > 0 AND " & _
             "InStr(InStr(1, [Name], 'John') + 4, [Name], 'Doe') > 0"
    
    ' Execute and display results
    ExecuteAndDisplay strSQL
End Sub

Dynamic Multi-Word Search

vb
' Handle searches with multiple words
Private Sub HandleDynamicSearch(searchText As String)
    Dim words() As String
    Dim strSQL As String
    Dim i As Integer
    
    words = Split(searchText, " ")
    
    If UBound(words) = 0 Then
        ' Single word search
        strSQL = "SELECT * FROM Customers WHERE Name LIKE '%" & searchText & "%'"
    Else
        ' Multiple word search - look for words in order
        strSQL = "SELECT * FROM Customers WHERE "
        
        For i = 0 To UBound(words)
            If i > 0 Then strSQL = strSQL & " AND "
            strSQL = strSQL & "InStr(1, [Name], '" & words(i) & "') > " & IIf(i > 0, _
                "InStr(1, [Name], '" & words(i-1) & "') + " & Len(words(i-1)), "0")
        Next i
    End If
    
    ExecuteAndDisplay strSQL
End Sub

Performance Considerations

vb
' Optimize for large recordsets
Private Sub OptimizedSearch(searchText As String)
    ' Create a temporary index if frequently searching
    ' Or use server-side filtering as shown above
    ' Client-side filtering can be slow for large datasets
    
    ' Alternative: Use Find method with custom criteria
    rsCustomers.Filter = adFilterNone
    rsCustomers.MoveFirst
    
    Dim foundCount As Long
    foundCount = 0
    
    Do While Not rsCustomers.EOF
        If CheckWordOrder(rsCustomers!Name, searchText) Then
            foundCount = foundCount + 1
            ' Add to results or highlight in UI
        End If
        rsCustomers.MoveNext
    Loop
    
    MsgBox foundCount & " records found"
End Sub

Private Function CheckWordOrder(text As String, searchText As String) As Boolean
    Dim words() As String
    Dim positions() As Long
    Dim i As Integer
    
    words = Split(searchText, " ")
    ReDim positions(UBound(words))
    
    ' Find positions of each word
    For i = 0 To UBound(words)
        positions(i) = InStr(1, text, words(i))
        If positions(i) = 0 Then Exit Function
    Next i
    
    ' Check if words appear in order
    For i = 1 To UBound(positions)
        If positions(i) <= positions(i-1) Then Exit Function
    Next i
    
    CheckWordOrder = True
End Function

Sources

  1. ADO Filter Property - Microsoft Learn
  2. ADO Recordset Filter - DevGuru
  3. VBA Recordset Filter Wildcard Issues - Stack Overflow
  4. LIKE Operator in ADO vs SQL Server - Stack Overflow
  5. Filter Property - Oracle Documentation

Conclusion

The ADODB.Recordset.Filter property in VB6 has limitations that prevent it from handling complex pattern matching like searching for words in order with arbitrary characters between them. While the Filter property works well for simple wildcard searches at the beginning or end of strings, more sophisticated text matching requires alternative approaches:

  1. Use SQL-based solutions with database-specific pattern matching functions like PATINDEX (SQL Server) or InStr (Access)
  2. Implement client-side filtering with custom logic when server-side filtering isn’t feasible
  3. Consider full-text search capabilities if available in your database
  4. Pre-process your queries to handle multi-word searches by building dynamic SQL statements

For your specific case of searching for “John” followed by “Doe” with characters between them, the recommended approach is to modify your SQL query to use the database’s native string functions rather than relying on the ADO Filter property’s limited pattern matching capabilities.

How can I implement case-insensitive text filtering in VB6's ADODB.Recordset?What are the performance implications of using client-side filtering vs. server-side queries in VB6?How to handle special characters and apostrophes in VB6 ADO Filter queries?What alternatives exist to the ADO Filter property for complex text searches in VB6?How to implement multi-word search with exact word order in VB6 applications?Can I use regular expressions with VB6's ADODB.Recordset for advanced pattern matching?
Ask NeuroAgent