Solved

SQL Query Generates no Data

Posted on 2013-01-21
3
426 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now