title: VBA Recordset Filter Wildcard 'Ending With' Not working- Error 3001
body: <p>I am reading an Excel file into an ADO Recordset using <code>Microsoft.ACE.OLEDB.12.0</code>. As per snb's excellent <a href="http://www.snb-vba.eu/VBA_ADODB_recordset_en.html#L_11" rel="nofollow noreferrer">article</a> 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 <code>Error 3001: "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"</code>. The "Ends With" filter is essentially a wildcard filter, just like one would use in SQL, and the syntax is as follows: </p>
  
  <p><code>"[FieldName] LIKE '*searchterm'"</code>. </p>
  
  <p>Saying that I have tried all combinations of wildcard characters (<code>*, %</code>) with no luck. The <a href="https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/filter-property?view=sql-server-2017" rel="nofollow noreferrer">microsoft documentation</a> on the Recordset Filter property says the following about the use of the LIKE operator:</p>
  
  <p>"In a LIKE clause, you can use a wildcard at the beginning and end of the pattern. For example, you can use LastName Like '<em>mit</em>'. Or with LIKE you can use a wildcard only at the end of the pattern. For example, LastName Like 'Smit*'.</p>
  
  <p>The above seems unclear, but I believe that they are saying the following:</p>
  
  <p>Beginning And End: LastName Like '<em>mit</em>' is legal 
  Begins With: LastName Like 'Smit*' is legal </p>
  
  <p>BUT </p>
  
  <p>End With: LastName Like '*t' Is Illegall???</p>
  
  <p>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. </p>
  
  <p>If not, then I hope that someone can point me to what I am missing. All suggestions, ideas, or answers are greatly appreciated. </p>
  
  <p>Below is my code: </p>
  
  <pre><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=" &amp; strSourceFile &amp; _
               ";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" &amp; _
              " 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
  </code></pre>
  
  <p>Sample Data Example: </p>
  
  <p><a href="https://i.stack.imgur.com/nKnD1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nKnD1.png" alt="enter image description here"></a></p>
  
  <p>Also, note that the data type of the field is 
  adVarWChar, DataTypeEnum = 202, which Indicates a null-terminated Unicode character string.</p>