I got this working it was just a manner of putting the spaces in the right places and or adding some.

Much like whiz said...

For anyone doing projects like this I found it easier to do my sql outside the VBA editor and then copy it back into the script. I also did a small bit of code at a time till it all worked correcly.

Any Thanks All for the help...

Mike


Sub junker2()
    
    Dim sSql As String
    
    
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=TESTBOX;DBQ=TEST;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;S" _
        ), Array("SL=;SIGNON=;")), Destination:=Range("A2"))
        
              
       
    strSQL = "SELECT ORDER.ODIV, ORDER.ODIVI, ORDER.DACUST, ORDER.CUS, ORDER.OMON, ORDER.ODAY, ORDER.OYER, ' ', ORDER.OLAB,  SUFP.NSUFFX,ORDER.DANUM, ORDER.DASEQ, ORDER.DAPST, ' ',SUM(CASE WHEN XREF.RFSUM = 'EA' THEN (ODET.LQDO / XREF.RFCQTY) WHEN XREF.RFSUM = 'IT' THEN (ODET.LQDO / XREF.RFCIQY) "
    strSQL = strSQL & "ELSE ODET.LQDO END),' ', Count(*), Sum(ODET.LQDO), ' ', ORDER.CCO1, ORDER.CCO2, ORDER.CCO3,ORDER.DASCTY, ORDER.DASST, ORDER.OZIP "
    strSQL = strSQL & "FROM NYSYC.ODET ODET "
    strSQL = strSQL & "INNER JOIN NYSYC.ORDER ORDER ON ORDER.ODIV = ODET.ODIVI AND ORDER.DANUM = ODET.ODNUM AND ORDER.DASEQ = ODET.ODSEQ AND ORDER.OLAB = ODET.ODLAB "
    strSQL = strSQL & "INNER JOIN PRO6.SUFP SUFP ON (CASE WHEN ORDER.OSR3 <> '' THEN ORDER.OSR3 ELSE ORDER.OLAB END) = SUFP.ONC "
    strSQL = strSQL & "LEFT OUTER JOIN NYSYC.PLIFT XREF ON (CASE WHEN ORDER.OSR3 <> '' THEN ORDER.OSR3 ELSE ORDER.OLAB END) = XREF.RFLAB AND ODET.ODPROD = XREF.RFPRD AND XREF.RFSTS = 'ACT' "
    strSQL = strSQL & "WHERE  ((ORDER.ODIVI='45') AND (ORDER.DAPST=15) AND (ORDER.OYER=83)) "
    strSQL = strSQL & "GROUP BY ORDER.ODIV, ORDER.ODIVI, ORDER.DACUST, ORDER.CUS, ORDER.OMON, ORDER.ODAY, ORDER.OYER, ORDER.OLAB, ORDER.DANUM, ORDER.DASEQ, ORDER.DAPST, ORDER.CCO1, ORDER.CCO2, ORDER.CCO3, SUFP.NSUFFX, ORDER.OZIP, ORDER.DASCTY, ORDER.DASST "
 
    .CommandText = strSQL
    .Refresh BackgroundQuery:=False
    End With
 
   
End Sub