Solved

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

Posted on 2009-04-02
2
556 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
2 Comments
 
LVL 1

Accepted Solution

by:
witwit earned 500 total points
Comment Utility
Simple, replace 'Ter*' with 'Ter%'

:-)
0
 

Author Closing Comment

by:jm302
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

744 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

18 Experts available now in Live!

Get 1:1 Help Now