excel - VBA 记录集筛选器通配符 'Ending With' 不起作用 - 错误 3001

Question

我正在使用 Microsoft.ACE.OLEDB.12.0 将 Excel 文件读入 ADO 记录集。根据snb的优秀article在 ADO 上，我一直在使用许多工作完美的过滤器，除了“Ends With”过滤器，我收到了 Error 3001: "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" 。 “Ends With”过滤器本质上是一种通配符过滤器，就像在 SQL 中使用的一样，语法如下:

"[FieldName] LIKE '*searchterm'" 。

说我已经尝试了通配符的所有组合( *, % )，但没有成功。 microsoft documentation Recordset Filter 属性上有关 LIKE 运算符的使用的说明如下:

“在 LIKE 子句中，您可以在模式的开头和结尾使用通配符。例如，您可以使用 LastName Like 'mit'。或者对于 LIKE，您可以使用通配符仅在模式末尾。例如，LastName Like 'Smit*'。

上面的内容似乎不清楚，但我相信他们说的是以下内容:

开始和结束:姓氏类似“mit”是合法的开头为:姓氏如“Smit*”是合法的

但是

结尾为:像“*t”这样的姓氏是非法的？？？

如果是这种情况，那么我知道“Ends with”过滤器不可能像人们直观地认为的那样，并且我希望有人可以提供一种替代解决方案来模仿“Ends With”通配符过滤器。

如果没有，那么我希望有人能指出我所缺少的内容。非常感谢所有建议、想法或答案。

下面是我的代码:

Option Explicit
Sub SheetToRecrdset()

 Dim strSourceFile As String
 Dim Conn As New ADODB.Connection
 Dim RcrdsetSheet As ADODB.Recordset

  strSourceFile = ThisWorkbook.FullName

    Set Conn = New ADODB.Connection
        With Conn
             .Provider = "Microsoft.ACE.OLEDB.12.0"
             .ConnectionString = "Data Source=" & strSourceFile & _
             ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"
             .Open
        End With

    Set RcrdsetSheet = New ADODB.Recordset

     RcrdsetSheet.Open "SELECT * FROM [Data$] Where Not IsNull([Row_ID])", _
     Conn, adOpenKeyset, adLockOptimistic, adCmdText

        If RcrdsetSheet.EOF = True Or RcrdsetSheet.BOF = True Then
            MsgBox "For some reason the query did not return any data. Try closing" & _
            " and and opening the file again.", vbCritical, "Error: No Results Returned"
            Exit Sub
        End If

        'Example String to filter: "MRQ"

        'CONTAINS FILTER WORKS
        RcrdsetSheet.Filter = "[LOCATION] LIKE '*M*'"

        'BEGINS WITH FILTER WORKS
        RcrdsetSheet.Filter = "[LOCATION] LIKE 'M*'"


       'ENDS WITH FILTER DOESN'T WORK
       'RcrdsetSheet.Filter = "[LOCATION] LIKE '*Q'"     'Error 3001

       '@TinMan's Suggestions
       'RcrdsetSheet.Filter = "[LOCATION] LIKE '*Q''"    'Error 2147024809
       'RcrdsetSheet.Filter = "[LOCATION] LIKE '*Q'''"   'Error 3001
       RcrdsetSheet.Filter = "[LOCATION] LIKE ""*Q'"""   'Returns no Records

       Debug.Print RcrdsetSheet.RecordCount 'Returns 0

       Dim arrayOut As Variant

       arryOut = RcrdsetSheet.GetRows       'Error 3021


End Sub

示例数据示例:

另外，请注意该字段的数据类型是adVarWChar, DataTypeEnum = 202，表示以 null 结尾的 Unicode 字符串。

Answer 1

新答案

我原来的答案有一些关于在查询中使用引号和单打的有值(value)的信息，但没有解决OP的问题。

虽然不太清楚，但摘录自 ADO » Recordset » Filter解释一下

If you use the LIKE operator, you can also use the * or % wildcards as the last character in the string or as the first and last character in the string.

将 LIKE 运算符与 ADODB 记录集筛选器属性一起使用时，如果筛选器以通配符(* 或 %)开头，则它必须以通配符(* 或 %)结尾。过滤器字符串中间的通配符不起作用。

因此，仅仅因为我们不能在使用 ADO 记录集过滤器的 LIKE 运算符中使用通配符而不用通配符结束过滤器，并不意味着我们无法使其工作!

让我们破解系统!

• 向返回最后一个字符的查询添加计算字段
• 使用 % 作为 ADO 记录集过滤器的最后一个字符
• 使用 Like *somevalue% 和计算字段 = 最后一个字符过滤原始字段

测试代码

Sub Test()
    TestFilter "(Location Like '*Q%') AND (LOCATIONLastChar = '''')"
End Sub

Sub TestFilter(Filter As String)
    Const BaseConnectionString = "Data Source=@FullName;Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"
    Dim Conn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim SQL As String

    Set Conn = New ADODB.Connection
    With Conn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = Replace(BaseConnectionString, "@FullName", ThisWorkbook.FullName)
        .Open
    End With

    SQL = "SELECT *, Right(LOCATION,1) AS LOCATIONLastChar FROM [Data$] Where Not IsNull(Row_ID)"

    rs.Open SQL, Conn, adOpenKeyset, adLockOptimistic, adCmdText

    rs.Filter = Filter


    If Not rs.BOF Then
        Worksheets.Add
        Range("A1").CopyFromRecordset rs
    End If
    rs.Close
    Conn.Close
End Sub

原帖

当我在编写查询时遇到问题时，我会使用 Access 查询设计器来帮助我编写查询。

SELECT Table1.Field1
FROM Table1
WHERE (((Table1.Field1) Like "a'*"));

请注意，设计器在过滤器字符串周围使用引号。 SQL 可以对字符串使用双引号或单引号。

如果对字符串值使用双引号，则双引号内的任何引号都需要加倍。

这是一个例子:

rs.Open "SELECT Table1.Field1 FROM Table1 WHERE (((Table1.Field1) Like ""a'*""));"

然而，要正确地做到这一点是一件令人头疼的事情。所以我所做的就是编写几个辅助函数，为我加倍引号或单引号。

Function getClipBoardText()
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .getFromClipBoard
        getClipBoardText = .getText
    End With
End Function

Sub PrintText()
    Dim s As String
    s = getClipBoardText
    Debug.Print Replace(s, Chr(34), String(2, 34))
End Sub

现在我所要做的就是复制设计器的 SQL 并运行 PrintText

长话短说，只需将单引号括在单引号内即可!

RcrdsetSheet.Filter = "[LOCATION] LIKE '*Q'''"

以下是如何在双引号内使用单引号

RcrdsetSheet.Filter = "[LOCATION] LIKE ""*Q'"""