Solved

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

Posted on 2009-04-02
2
565 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
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Outlook 2010 Attachment Issue 12 55
Canon C5035 Scan to multiple e-mail address domains 3 48
Link to a specific evernote 6 108
Open source office 5 17
Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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 …

830 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