?
Solved

SQL Query Generates no Data

Posted on 2013-01-21
3
Medium Priority
?
461 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
[X]
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
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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