VBA Recordset Filter Wildcard 'Ending With' Not working- Error 3001

Question

I am reading an Excel file into an ADO Recordset using Microsoft.ACE.OLEDB.12.0. As per snb's excellent article on ADO I have been using a number of filters that work perfectly, all except for the "Ends With" filter, for which I am receiving Error 3001: "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another". The "Ends With" filter is essentially a wildcard filter, just like one would use in SQL, and the syntax is as follows:

"[FieldName] LIKE '*searchterm'".

Saying that I have tried all combinations of wildcard characters (*, %) with no luck. The microsoft documentation on the Recordset Filter property says the following about the use of the LIKE operator:

"In a LIKE clause, you can use a wildcard at the beginning and end of the pattern. For example, you can use LastName Like 'mit'. Or with LIKE you can use a wildcard only at the end of the pattern. For example, LastName Like 'Smit*'.

The above seems unclear, but I believe that they are saying the following:

Beginning And End: LastName Like 'mit' is legal Begins With: LastName Like 'Smit*' is legal

BUT

End With: LastName Like '*t' Is Illegall???

If that's the case, then I know that an Ends with filter is not possible as one may intuitively think, and I hope someone could provide an alternative solution that will mimic an "Ends With" wildcard filter.

If not, then I hope that someone can point me to what I am missing. All suggestions, ideas, or answers are greatly appreciated.

Below is my code:

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

Sample Data Example:

Also, note that the data type of the field is adVarWChar, DataTypeEnum = 202, which Indicates a null-terminated Unicode character string.

Answer 1

New Answer

My original answer has some valuable information about using quotes and singles inside queries but does not address the OP's question.

Although not really clear, this excerpt from ADO » Recordset » Filter explains it

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.

When using LIKE operator with the ADODB Recordset Filter property, if the Filter begins with a wildcard (* or %) then it must end with a wildcard (* or %). Wildcards in the middle of a Filter string do not work.

So just because we can not use a wildcard with the LIKE operator using an ADO Recordset Filter without ending the Filter with a wildcard doesn't mean that we cannot make it work!

Let's Hack the System!

• Add a calculated field to the query that returns the last character
• Use % for the last character of the ADO Recordset Filter
• Filter the original field using Like *somevalue% and calculated field = last character

Test Code

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

Original Post

When I have a problem writing a query I use the Access Query Designer to help me write it.

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

Notice that the designer uses quotes around the filter string. SQL can use either double or single quotes for strings.

If you use double quotes for string values, any quotes within the double quotes need to be doubled.

Here is an example:

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

This is, however, be a pain in the neck to get right. So what I do is write a couple of helper functions that will double the quotes or single quotes for me.

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

Now all I have to do is copy the designer's SQL and run PrintText

To make a very long story short, simply double the single quote that is wrapped inside single quotes!

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

Here is how you would use the single quotes inside of double quotes

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