Microsoft Query in VBA

Hi, Need a little help using Microsoft Qurey, I recorded a macro of my SQL script, however excel returns it from the macro recorder as a syntax error.


The recorder added some chr(#) in as well Is there a way I can just put the code in a string and pass it over to the query to run?



Can someone please advise,

Thank You, Mike

Here is the code recorded:

With Selection.QueryTable
      .Connection = Array(Array( _
      "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=TESTBOX;DBQ=TEST1;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;S" _
      ), Array("SL=;SIGNON=;"))
      .CommandText = Array( _
      "SELECT ORDER.DIV, ORDER.USER, ORDER.OHCUST, ORDER.CUS, ORDER.MON, ORDER.DAY, ORDER.YEAR, ' ', ORDER.LAB,SUFFMSTP.NSUFFX, ORDER.OHNUM, ORDER.OHSEQ, ORDER.OHPST, ' ',SUM(CASE" _
      , _
      " WHEN XREF.RFSUM = 'EA' THEN (DET.ODLQYO / XREF.RFCQTY) WHEN XREF.RFSUM = 'IT' THEN (DET.ODLQYO / XREF.RFCIQY) "&chr(13)&""&chr(13)&""&chr(10)&"ELSE DET.ODLQYO END), ' ', Count(*), Sum(DET.ODLQYO), ' ', ORDER.OHCOM1, " _
      , _
      "ORDER.OHCOM2, ORDER.OHCOM3"&chr(13)&""&chr(10)&""&chr(13)&""&chr(13)&""&chr(10)&""&chr(13)&""&chr(13)&""&chr(10)&"FROM NBJDTA.DET DET "&chr(13)&""&chr(13)&""&chr(10)&"INNER JOIN NBJDTA.ORDER ORDER ON ORDER.DIV = DET.ODDIV AND ORDER.OHNUM = DET.ODNUM AND ORDER.OHSEQ = DET.ODSEQ AND ORDH" _
      , _
      "DR.LAB = DET.ODLAB"&chr(13)&""&chr(13)&""&chr(10)&"INNER JOIN PRODFA.SUFFMSTP SUFFMSTP ON (CASE WHEN ORDER.OHXSR6 <> '' THEN ORDER.OHXSR6 ELSE ORDER.LAB END) = SUFFMSTP.NCO"&chr(13)&""&chr(13)&""&chr(10)&"LEFT OUTER JOIN NBJDTA.PDMSTILF XREF ON (CASE" _
      , _
      " WHEN ORDER.OHXSR6 <> '' THEN ORDER.OHXSR6 ELSE ORDER.LAB END) = XREF.RFLAB AND DET.ODPROD = XREF.RFPRD AND XREF.RFSTS = 'ACT'"&chr(13)&""&chr(13)&""&chr(10)&"WHERE  ((ORDER.USER='45') AND (ORDER.OHPST=15) AND (ORDER." _
      ,,)
      .Refresh BackgroundQuery:=False
  End With

The SQL looks like this:

SELECT ORDER.DIV, ORDER.USER, ORDER.OHCUST, ORDER.CUS, ORDER.MON, ORDER.DAY, ORDER.YEAR, ' ', ORDER.LAB, ORDER.OHNUM, ORDER.OHSEQ, ORDER.OHPST, ' ', ' ', ' ', ' ', Count(*), Sum(DET.ODLQYO), ' ', ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3,
SUM(CASE WHEN XREF.RFSUM = 'EA' THEN (DET.ODLQYO / XREF.RFCQTY) WHEN XREF.RFSUM = 'IT' THEN (DET.ODLQYO / XREF.RFCIQY) 
ELSE DET.ODLQYO END), SUFFMSTP.NSUFFX
 
FROM NBJDTA.DET DET 
INNER JOIN NBJDTA.ORDER ORDER ON ORDER.DIV = DET.ODDIV AND ORDER.OHNUM = DET.ODNUM AND ORDER.OHSEQ = DET.ODSEQ AND ORDER.LAB = DET.ODLAB
INNER JOIN PRODFA.SUFFMSTP SUFFMSTP ON (CASE WHEN ORDER.OHXSR6 <> '' THEN ORDER.OHXSR6 ELSE ORDER.LAB END) = SUFFMSTP.NCO
LEFT OUTER JOIN NBJDTA.PDMSTILF XREF ON (CASE WHEN ORDER.OHXSR6 <> '' THEN ORDER.OHXSR6 ELSE ORDER.LAB END) = XREF.RFLAB AND DET.ODPROD = XREF.RFPRD AND XREF.RFSTS = 'ACT'
WHERE  ((ORDER.USER='45') AND (ORDER.OHPST=15) AND (ORDER.YEAR=83))
 
GROUP BY ORDER.DIV, ORDER.USER, ORDER.OHCUST, ORDER.CUS, ORDER.MON, ORDER.DAY, ORDER.YEAR, ORDER.LAB, ORDER.OHNUM, ORDER.OHSEQ, ORDER.OHPST, ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3, SUFFMSTP.NSUFFX