Running query from Excel VBA returns null results

[Mike1138]

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:

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:

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?

 

[Mike1138]

bump

 

[ukmikeb]

Hi

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

hth

 

[Mike1138]

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.

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