Excel VBA database query fails using ADO.

Hello,

I'm having a problem with an Excel VBA lookup query using ADO.

Hopefully I've missed something obvious to someone with more database experience.
I built this ADO class myself, but I have only a little database experience gained by trail and error.

My MS Access .accdb supplier database contains several firms which begin with the word steel.
When I query Steel% I get nothing back, but if I query Steel%Stock% it works perfectly.

A user form generates the filter from user input boxes and then submits the query to the ADO class.

I have used debug.print to watch the sFilter being generated and the result is Steel%


The query is done by an ADO class which uses SQL Like
this is the code used to call the class method

vData = myADOdb.Query_Like(sFilter)

This is the code in the class method

Public Function Query_Like(ByVal LookUp_Value As String) As Variant
' Looks up a Look_Value from the currently defined
' database, table, and LookUpField
    
Dim rsData As ADODB.Recordset
Dim sConnect, sSQL As String
Dim vData As Variant
Dim rowCount As Long

    On Error GoTo ErrorHandler:
    
    If pProvider = "" Then
        Err.Raise Number:=vbObjectError + 1024, _
        Description:="The database provider must be set before using this class"
        Exit Function
    End If
    
    'LookupValue = "%" & LookupValue
    
    ' Create the connection string
    sConnect = pProvider & "Data Source =" & pPath & pName
    
    ' Create the SQL Statement.
    sSQL = "SELECT * " & "FROM " & pTable & " " & "WHERE [" & pLookUpField & "] LIKE '" & LookUp_Value & "%'"
    
    ' Create the Recordset object
    Set rsData = New ADODB.Recordset
    
    'run the query.
    rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

   
    ' If we're Not at the End of file Nor at the Begining of File
    If Not (rsData.BOF And rsData.EOF) Then
        
        ' Transpose the recordset and copy it to a variant
        vData = myTranspose(rsData.GetRows)
        
        rowCount = UBound(vData, 1) + 1
        
        If rowCount = 0 Then
        'Cry
        End If
        
        Query_Like = vData
        
        ' Close the Recordset object.
        rsData.Close
        
    Else
        ' Close the Recordset object.
        rsData.Close
        
        Err.Raise Number:=vbObjectError + 1024, _
        Description:="No records found for " & LookUp_Value & " in field " & pLookUpField
        
        'Query_Like = LookUp_Value & " not found in field " & pLookUpField
        Query_Like = Array(-1, 0)
         
    End If
    
    ' Destroy the Recordset object.
    Set rsData = Nothing
    
    'quit the function to avoid an error in Errorhandler
    Exit Function
    
    
ErrorHandler:
        Err.Raise Number:=vbObjectError + 1024, _
        Description:="No records found for " & LookUp_Value & " in field " & pLookUpField
        
        rsData.Close  ' Close the Recordset object.
        Query_Like = Array(-1, 0)
End Function

are you certain there is nothing (eg leading space) before the 'Steel' in the name?

I double checked the sFilter result to be sure. There are no leading characters. I even tried using trim(sFilter) to be sure. The result is the same.

I meant in the data, not in the filter criteria

does it work if you use %Steel% instead for instance?

I found the problem. I had a logic error using ubound to get a rowcount. The problem is solved.