Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Query Generates no Data

Posted on 2013-01-21
3
441 Views
Last Modified: 2013-01-21
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

0
Comment
Question by:dec789
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38803743
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
 

Author Closing Comment

by:dec789
ID: 38803753
Thank you mbizup!  That worked perfectly!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38803783
Glad to help out :-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question