SQL Query Generates no Data

The code below does not work when I attempt to use a wildcard in the WHERE clause.
The code below does work if I remove the wild card (asterisk).

The code below generates this SQL statement when I include the wildcard.
SELECT * FROM view_eCAP_DATA_EXCEL WHERE WBS LIKE '433*'
and generates this SQL statement when I remove the wild card.
SELECT * FROM view_eCAP_DATA_EXCEL WHERE WBS LIKE '433CA'

Private Sub UpdateQueryTable_BySubsystem(myPath, myFile, myViewName, myDefaultDir, myDestination, myDataSheet, myFieldToFilter)

    On Error GoTo Error_Check

    Dim mySQL As String
    Dim myConnection As Variant
    Dim mySubSystem As String
    Dim cn As Object
    Dim rs As Object
    Dim strFile As String
    Dim strCon As String
    Dim strSQL As String
    Dim s As String
    Dim i As Integer, j As Integer
    Dim ws As Worksheet
    Dim iCols As Integer
    
    ' Path to and file name of Access database
    strFile = myPath & myFile

    mySubSystem = Range("WBSSelect")
    
    ' Set connection string to Access DB
    myConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";"
    ' Late binding, so no reference is needed
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    ' Open connection to Access DB
    cn.Open myConnection

    ' Build SQL Statement
    If Len(myFieldToFilter) > 1 Then

        mySQL = "SELECT * " & _
              "FROM " & myViewName & " " & _
              "WHERE " & myFieldToFilter & " LIKE '" & mySubSystem & "*' "
    Else
        mySQL = "SELECT * FROM " & myViewName
    End If
   
    Debug.Print mySQL
    
    Sheets(myDataSheet).Select
    Range(myDestination).Select

    rs.Open mySQL, cn, 3, 3
    
    ' Include Column Headers in record set
    Set ws = Worksheets(myDataSheet)
    For iCols = 0 To rs.Fields.count - 1
        ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
    Next
    ws.Range(ws.Cells(1, 1), _
        ws.Cells(1, rs.Fields.count)).Font.Bold = True
    ws.Range("A2").CopyFromRecordset rs

    ' Finish
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    Set ws = Nothing

Exit Sub
Exit_ErrorCheck:
    End
Error_Check:
        MsgBox Err.Description
        Resume Exit_ErrorCheck
End Sub

Open in new window

dec789Asked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
For an ADO recordset, use % for the wildcard with LIKE instead of *:

 mySQL = "SELECT * " & _
              "FROM " & myViewName & " " & _
              "WHERE " & myFieldToFilter & " LIKE '" & mySubSystem & "%' "

Open in new window

0
 
dec789Author Commented:
Thank you mbizup!  That worked perfectly!
0
 
mbizupCommented:
Glad to help out :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.