Like statment

[brew]

Hi,

Is there a way to search entire fields for example...

Search for "mer"

and return the field with "Homer"



Currently I'm using a LIKE function but I'm open to anything.

Private Function BuildFilter() As Variant

    Dim varWhere As Variant


    varWhere = Null 
   
    
    ' Check for LIKE Patient Name
    If Me.txtpatname > "" Then
        varWhere = varWhere & "[PatientName] LIKE """ & Me.txtpatname & "*"" AND "
    End If
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

[June7]

Like is the correct operator. You might need the wildcard on both sides of the input.

[brew]

Thanks June7

After a bit of playing around with wildcard location the following seem to of worked.

Private Function BuildFilter() As Variant

    Dim varWhere As Variant


    varWhere = Null  ' Main filter
   
    
    ' Check for LIKE Patient Name
    If Me.txtpatname > "" Then
        varWhere = varWhere & "[PatientName] LIKE ""*" & Me.txtpatname & "*"" AND "
    End If
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function