Running query from Excel VBA returns null results

Mike1138

Board Regular
Joined
Jun 8, 2013
Messages
54
Hi everyone,

I have a bit of code that
  1. Connects to an Access database
  2. Constructs an SQL string
  3. Queries the database
  4. Returns the results in an Excel Table

When I execute the code, I get no results -- the table is completely empty.

Thinking I'd messed up the SQL, I ran the SQL statement in Access and received the desired results.

I saved the query in Access and attempted to retrieve the results in Excel using Data -> Get External Data -> Access and my results are again null.

Here is the SQL as it appears in Access:
Code:
SELECT Q.Yr, Q.Abbr Count(Q.ClaimNum) AS Claims


FROM 
   (SELECT tblBU.Abbr, Year(tblClaims.LossDate) AS Yr, 
    tblOSHAHistory.ClaimNum 
    FROM tblBU INNER JOIN (tblBranch INNER JOIN 
    (tblClaims INNER JOIN tblOSHAHistory ON 
    tblClaims.ClaimNum=tblOSHAHistory.ClaimNum) 
    ON tblBranch.LocFRU=tblClaims.LocFRU) ON 
    tblBU.ID=tblBranch.BUID 
    GROUP BY tblBU.Abbr, 
    Year(tblClaims.LossDate), tblOSHAHistory.ClaimNum, 
    tblOSHAHistory.ChngDate, tblOSHAHistory.OSHAStat 
    HAVING (((Year(tblClaims.LossDate))=2013) AND 
    ((tblOSHAHistory.ChngDate)=
        (SELECT Max(O.ChngDate) 
         FROM tblOSHAHistory as O 
         WHERE O.ClaimNum = tblOSHAHistory.ClaimNum)) 
         AND ((tblOSHAHistory.OSHAStat) Like "R*")))  
AS Q


GROUP BY Q.Abbr, Q.Yr;

And here is the SQL as it is constructed by code:

Code:
sSQLSelect = "SELECT Q.Yr, Q.Abbr AS BU, Count(Q.ClaimNum) AS Claims"


sSQLFrom = "FROM " _
                & "(SELECT tblBU.Abbr, Year(tblClaims.LossDate) AS Yr, " _
                & "tblOSHAHistory.ClaimNum " _
                & "FROM tblBU INNER JOIN (tblBranch INNER JOIN " _
                & "(tblClaims INNER JOIN tblOSHAHistory ON " _
                & "tblClaims.ClaimNum=tblOSHAHistory.ClaimNum) " _
                & "ON tblBranch.LocFRU=tblClaims.LocFRU) ON " _
                & "tblBU.ID=tblBranch.BUID " _
                & "GROUP BY tblBU.Abbr, " _
                & "Year(tblClaims.LossDate), tblOSHAHistory.ClaimNum, " _
                & "tblOSHAHistory.ChngDate, tblOSHAHistory.OSHAStat " _
                & "HAVING (((Year(tblClaims.LossDate))=" & [B2] & ") AND " _
                & "((tblOSHAHistory.ChngDate)=" _
                    & "(SELECT Max(O.ChngDate) " _
                    & "FROM tblOSHAHistory AS O " _
                    & "WHERE O.ClaimNum = tblOSHAHistory.ClaimNum)) " _
                    & "AND ((tblOSHAHistory.OSHAStat) Like " & Chr(34) & "R*" & Chr(34) & "))) " _
            & "AS Q"




sSQLSort = "GROUP BY Q.Abbr, Q.Yr;"

Can anyone spot a difference that would return a null set? Is there a bug in running subqueries from VBA?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

You have broken the SQL into three strings it should be one!

hth
 

Mike1138

Board Regular
Joined
Jun 8, 2013
Messages
54
Hi

You have broken the SQL into three strings it should be one!

hth


No, it'll work fine as three as long as they are concatenated in the on the command line.

Code:
.CommandText = sSQLSelect & " " & sSQLFrom & " " & sSQLSort
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top