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


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!

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
     ActiveSheet.Range("W2").CopyFromRecordset MyRecordset
' Column Labels
    With ActiveSheet.Range("W1:Y1")
        .Value = Array("RankLvl", "Geo", "Total")
    End With
    MsgBox "Done!"
End Sub

Open in new window

Who is Participating?
witwitConnect With a Mentor Commented:
Simple, replace 'Ter*' with 'Ter%'

jm302Author Commented:
Bah!  How embarrassing! Well, I can still swallow my pride and be thankful to get the solution.  So, thank you very much!  :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.