Can Excel run this big SQL?

[bobdole22]

Here is the full string:

SELECT tbl_ALLStatus.Status, Sum(Data.PBO) AS SumOfPBO, Sum([PBO]/[ABI]) AS Borrs, Sum([PBO])/Sum([pbo]/[abi]) AS Borr_ABI, Count(Data.BorrowerUniqueID) AS Loans, tbl_ALLStatus.StatusOrder, Sum(IIf([LoanTypeName]="Consolidation",[PBO],0)) AS Cons_PBO, Sum(IIf([LoanTypeName]<>"Consolidation",[PBO],0)) AS NonCons_PBO
FROM ((Data LEFT JOIN tbl_ALLStatus ON Data.Status = tbl_ALLStatus.Status) LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID) LEFT JOIN tbl_ALLLoanTypes ON (Data.LoanType = tbl_ALLLoanTypes.LoanType) AND (Data.LoanProgram = tbl_ALLLoanTypes.LoanProgram)
WHERE (((Data.PBO)<>0))
GROUP BY tbl_ALLStatus.Status, tbl_ALLStatus.StatusOrder
ORDER BY tbl_ALLStatus.StatusOrder;

It obviously works in Access. And I can't quite find the "<>" bracket spacing your talking about.

[Kenneth Hobs]

I would break that into 2 arrays. The first would be line one and use the comma delimiter. Break that first line of SQL into several small parts for each element where the comma is left off to be readded by a Join().

The second would poke the first Join() string from array one into the first element of array two. Add the other lines of SQL. Use the vbCRLF delimiter for the Join() of array 2.

[bobdole22]

The second would poke the first Join() string from array one int

I'm not sure what you mean by that. I'm sure I'm way off here, but what about this:

'So big it needs a two parter
        Dim twoparter As String
        
            ReDim s(1 To 9)
            s(1) = "SELECT tbl_ALLStatus.Status,"
            s(2) = " Sum(Data.PBO) As SumOfPBO,"
            s(3) = " Sum([PBO]/[ABI]) As Borrs,"
            s(4) = " Sum([PBO])/Sum([pbo]/[abi]) As Borr_ABI,"
            s(5) = " Count(Data.BorrowerUniqueID) As Loans, tbl_ALLStatus.StatusOrder,"
            s(6) = " Sum(IIf([LoanTypeName]='Consolidation',"
            s(7) = "[PBO],0)) As Cons_PBO,"
            s(8) = " Sum(IIf([LoanTypeName]<>'Consolidation',"
            s(9) = "[PBO],0)) As NonCons_PBO "
            
            twoparter = Join(s(), ",")
            
            ReDim s(1 To 5)
            s(1) = "FROM ((Data LEFT JOIN tbl_ALLStatus ON Data.Status = tbl_ALLStatus.Status)"
            s(2) = "LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID) "
            s(3) = "LEFT JOIN tbl_ALLLoanTypes ON (Data.LoanType = tbl_ALLLoanTypes.LoanType)"
            s(4) = " And (Data.LoanProgram = tbl_ALLLoanTypes.LoanProgram) "
            s(5) = "WHERE (((Data.PBO)<>0));"
            
            twoparter = twoparter + Join(t(), vbCrLf)
            
            sSQL = twoparter
            'This statement will display qry_Report3

[Kenneth Hobs]

You left the comma at the end and then added another with the first Join(). Use & for string concatenation. Use + to add numbers.

Sub ken()
  Dim sSQL As String, s() As String
  ReDim s(1 To 8)
  s(1) = "SELECT tbl_ALLStatus.Status"
  s(2) = "Sum(Data.PBO) As SumOfPBO"
  s(3) = "Sum([PBO]/[ABI]) As Borrs"
  s(4) = "Sum([PBO])/Sum([pbo]/[abi]) As Borr_ABI"
  s(5) = "Count(Data.BorrowerUniqueID) As Loans"
  s(6) = "tbl_ALLStatus.StatusOrder"
  s(7) = "Sum(IIf([LoanTypeName]='Consolidation',[PBO],0)) As Cons_PBO"
  s(8) = "Sum(IIf([LoanTypeName] <> 'Consolidation', [PBO], 0)) As NonCons_PBO"
  sSQL = Join(s(), ",")
  ReDim s(1 To 5)
  s(1) = sSQL
  s(2) = "FROM ((Data LEFT JOIN tbl_ALLStatus ON Data.Status = tbl_ALLStatus.Status) LEFT JOIN tbl_ABI ON Data.BorrowerUniqueID = tbl_ABI.BorrowerUniqueID) LEFT JOIN tbl_ALLLoanTypes ON (Data.LoanType = tbl_ALLLoanTypes.LoanType) And (Data.LoanProgram = tbl_ALLLoanTypes.LoanProgram)"
  s(3) = "WHERE (((Data.PBO) <> 0))"
  s(4) = "GROUP BY tbl_ALLStatus.Status, tbl_ALLStatus.StatusOrder"
  s(5) = "ORDER BY tbl_ALLStatus.StatusOrder;"
  sSQL = Join(s(), vbCrLf)
  MsgBox sSQL
  Debug.Print sSQL
End Sub