Solved

SQL Query Generates no Data

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

803 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