Union all strSQL string VS. VBA editor 1

[ETID]

Hi All,...

My question is this, how do I get the vba editor to let me break this sql statement down so that I can read it in the window?

something like this....

strSQL = "SELECT * INTO vba_ETI_JMF_Table FROM (SELECT _ [MTLCLASN],[ENGMET],[SUPLCODE],[JOBMXREF], _[JOBMXFYR],DESIGNGN01 AS TARGET, SIEVESIZ01 AS _ ELEMENT,RSLTSEQN01 AS SEQUENCE FROM CAMSPROD_TJBMXFM WHERE _[JOBMXFYR]>=[Earliest JMF Year?] AND [SUPLCODE]=[Supplier _ code?] UNION ALL SELECT [MTLCLASN],[ENGMET],[SUPLCODE], _[JOBMXREF],[JOBMXFYR],DESIGNGN02 AS TARGET,SIEVESIZ02 AS _ ELEMENT,RSLTSEQN02 AS SEQUENCE FROM CAMSPROD_TJBMXFM WHERE _[JOBMXFYR]>=[Earliest JMF Year?] AND [SUPLCODE]=[Supplier _ code?]UNION ALL SELECT [MTLCLASN],[ENGMET],[SUPLCODE], _
...etc, etc.

When I am building the sql statement in the editor, it likes it and turns all text black as I add the "_" characters but when I'm done it errors out because the word "FROM" (at the begining of some lines)is treated like a new line instead of part of the string.


the whole statement....
--------------------
strSQL = "SELECT * INTO vba_ETI_JMF_Table FROM (SELECT [MTLCLASN],[ENGMET],[SUPLCODE],[JOBMXREF],[JOBMXFYR],DESIGNGN01 AS TARGET, SIEVESIZ01 AS ELEMENT,RSLTSEQN01 AS SEQUENCE FROM CAMSPROD_TJBMXFM WHERE [JOBMXFYR]>=[Earliest JMF Year?] AND [SUPLCODE]=[Supplier code?] UNION ALL SELECT [MTLCLASN],[ENGMET],[SUPLCODE],[JOBMXREF],[JOBMXFYR],DESIGNGN02 AS TARGET,SIEVESIZ02 AS ELEMENT,RSLTSEQN02 AS SEQUENCE FROM CAMSPROD_TJBMXFM WHERE [JOBMXFYR]>=[Earliest JMF Year?] AND [SUPLCODE]=[Supplier code?]UNION ALL SELECT [MTLCLASN],[ENGMET],[SUPLCODE],[JOBMXREF],[JOBMXFYR],DESIGNGN03 AS TARGET, SIEVESIZ03 AS ELEMENT,RSLTSEQN03 AS SEQUENCE FROM CAMSPROD_TJBMXFM WHERE [JOBMXFYR]>=[Earliest JMF Year?] AND [SUPLCODE]=[Supplier code?]);"

 

[RobBroekhuis]

You can't break in the middle of a literal string. Use instead something like:

strSQL = "SELECT * INTO vba_ETI_JMF_Table FROM (SELECT [MTLCLASN]," _
& "[ENGMET],[SUPLCODE],[JOBMXREF],[JOBMXFYR],DESIGNGN01 AS TARGET, SIEVESIZ01 AS ELEMENT,RSLTSEQN01 AS SEQUENCE FROM " _
& .... and so on...

Rob

 

[ETID]

Thanks I'll give it a whirl....and let you know how I made out!

 

[ETID]

Excellent...Thanks again