Solved

SQL Statement with JOIN in Excel VBA returns no records to Excel

Posted on 2009-04-02
2
570 Views
Last Modified: 2012-05-06
Hi!

I'm trying to pull some records from Access 2007 into Excel 2007 using a SQL statement with a JOIN (shown below) in VBA. I've been able make a SQL statement with no JOINs (also shown below) work just fine.  And both sets of SQL statements work fine and return records in Access. So, what am I doing wrong in the JOIN statement in VBA?

BTW, I tried to alias the table names to shorten the statement, but after I got no records back the the first time, I restored full table names.  Since I'm still getting no records, that was apparently not the problem.

Thanks in advance for your help!

Joel
Sub GetRankData()
 
' Variables
    Dim MyConnect As String
    Dim MyRecordset As ADODB.Recordset
    Dim MySQL As String, strEmptyRS As String
    
' Connection String
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= C:\DbaseTest\RankStaging.accdb"
 
' This statement works just fine
 MySQL = "SELECT 'Natl' AS RankLvl, Geo, SalesAmt AS Total FROM tblTestSalesScorecard WHERE Geo Like 'Terr%' " & _
         " AND Period = 'YTD' AND Type = 'Paper' ORDER BY 'Natl', SalesAmt DESC"
         
'  But this statement with a JOIN that ties in a different level of geographical hierarchy returns no records
 MySQL = "SELECT tblLookupTerritory.District AS RankLvl, tblStagingSalesVarTest.Geo, tblStagingSalesVarTest.Var " & _
         "FROM tblStagingSalesVarTest INNER JOIN tblLookupTerritory ON tblStagingSalesVarTest.Geo = tblLookupTerritory.Terr " & _
         "WHERE tblStagingSalesVarTest.Geo Like 'Ter*' And tblStagingSalesVarTest.Period = 'YTD' And tblStagingSalesVarTest.Type = 'Paper' " & _
         "ORDER BY tblLookupTerritory.District, tblStagingSalesVarTest.Var DESC"
 
' Create recordset
    Set MyRecordset = New ADODB.Recordset
    MyRecordset.Open MySQL, MyConnect, adOpenUnspecified, adLockReadOnly
 
' Copy recordset to Excel
     Sheets("Test").Select
     ActiveSheet.Range("W2").CopyFromRecordset MyRecordset
 
' Column Labels
    With ActiveSheet.Range("W1:Y1")
        .Value = Array("RankLvl", "Geo", "Total")
        .EntireColumn.AutoFit
    End With
    
    MsgBox "Done!"
 
End Sub

Open in new window

0
Comment
Question by:jm302
[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 Comments
 
LVL 1

Accepted Solution

by:
witwit earned 500 total points
ID: 24060803
Simple, replace 'Ter*' with 'Ter%'

:-)
0
 

Author Closing Comment

by:jm302
ID: 31565815
Bah!  How embarrassing! Well, I can still swallow my pride and be thankful to get the solution.  So, thank you very much!  :)
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

688 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