主题：阿可学艺[013]：请教：如何筛选出备注字段中包含多个字符串的记录？

例如：

有3个字符串，分别是str1、str2和str3。表中有个备注字段[备注]。我想筛选出[备注]同时包含str1、str2和str3三个字符串的记录。

 

请教了各位老师。

tables("xxx").Filter = "备注 like ‘*" & Str1 & "*' And 备注 Like '*" & Str2 & "*' And 备注 Like '*" & Str3 & "*'"

谢谢狐爸。

但是有个问题，如果其中有个字符串为空时会出错。如何解决？是否要用到selectcase语句呢？

你可以判断的啊：

 

Dim Filter As string

If Str1 > " Then

    Filter = "备注 like ‘*" & Str1 & "*'"

End if

If Str2 > "" Then

    if Filter > "" Then

       Filter = Filter & " And "

    End If

    Filter = Filter & "备注 like ‘*" & Str2 & "*'"

End If

If Str3 > "" Then

    if Filter > "" Then

          Filter = Filter & " And "

     End If

     Filter = Filter & "备注 like ‘*" & Str3 & "*'"

End If

Tables("XXX").Filter = Filter

 

建议看看：

http://www.foxtable.com/help/topics/1058.htm

 

动态合成Fileter字符串的另一种方法，这里可以是Str1～StrN,这个看起来不起眼的思路，可以为大家的模糊筛选、模糊查询组合条件字符串打开一个思路，基本上无需修改其他代码：

 

Dim Filter As String = """备注 Like '*" & Str1 & "*' And 备注 Like '*" & Str2 & "*' And 备注 Like '*" & Str3 & "*'""" '这里先写好Str1～StrN的全部条件字符串
If Str1 = Nothing OrElse Str2 = Nothing Then '这里用OrElse 从Str1判断至StrN－1
    Filter = Filter.Replace("备注 Like '**' And ",Nothing)
End If
If Str3 = Nothing Then ’这里必须是判断StrN
    Filter = Filter.Replace(" And 备注 Like '**'","")
    Filter = Filter.Replace("备注 Like '**'","") '加上这一句，就完全避免Str均为空的时候，Filter = "备注 Like '**'" 的问题

End If

 

加精啊

多列筛选或模糊查询的时候需要在这个的基础上加以修改，多列的话，嵌套循环应该很方便地解决了，主要更方便任意组合筛选。

如果特别多，可以用数组：

 

Dim Vals(10) As String
Dim Filter As String =
For Each Val As String In vals
    If Val > "" Then
        If filter > "" Then
            filter = filter & " And "
        End If
        filter = filter & "备注 Like '*"  & val & "*'"
    End If
Next
Tables("xxx").Filter = filter

我顶！

谢谢各位，我的方法如下，已试成功。敬请各位老师评点：

 

Dim Str0 As String = "000"
Dim Str1,Str2,Str3 As String
If Forms("窗口1").Controls("TextBox1").Value IsNot Nothing
    Str1 = Forms("窗口1").Controls("TextBox1").Value.Trim()
    Str0 = "1" & str0.SubString(1,2)
End If
If Forms("窗口1").Controls("TextBox2").Value IsNot Nothing
    Str2 = Forms("窗口1").Controls("TextBox2").Value.Trim()
    Str0 = str0.SubString(0,1) & "1" & str0.SubString(2,1)
End If
If Forms("窗口1").Controls("TextBox3").Value IsNot Nothing
    Str3 = Forms("窗口1").Controls("TextBox3").Value.Trim()
    Str0 = str0.SubString(0,2) & "1"
End If
Select Case Str0
    Case "000"
        Tables("表1").Filter = ""
    Case "100"
        Tables("表1").Filter = "[名称] like '*" & Str1 & "*'"
    Case "010"
        Tables("表1").Filter = "[名称] like '*" & Str2 & "*'"
    Case "001"
        Tables("表1").Filter = "[名称] like '*" & Str3 & "*'"
    Case "110"
        Tables("表1").Filter = "[名称] like '*" & Str1 & "*' And [名称] Like '*" & Str2 & "*'"
    Case "011"
        Tables("表1").Filter = "[名称] like '*" & Str2 & "*' And [名称] Like '*" & Str3 & "*'"
    Case "101"
        Tables("表1").Filter = "[名称] like '*" & Str3 & "*' And [名称] Like '*" & Str1 & "*'"
    Case "111"
        Tables("表1").Filter = "[名称] like '*" & Str1 & "*' And [名称] Like '*" & Str2 & "*' And [名称] Like '*" & Str3 & "*'"
End Select