Wrap SQL for VBA

G

Guest

I have a fairly long SQL string that I'd like to wrap since I can't fit in a
single line. I'm not sure how to do it, any help would be greatly appreciated.

SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date, [TLDSEU_CUR].ULO_NR AS
Trailer, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO.SRT_TYP_MNM_TE AS Origin, TLDSEU_CUR.DTN_CNY_CD + ' ' +
SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO_1.SRT_TYP_MNM_TE + '
' + TTYPSVC_DCO.SVC_TYP_MNM_TE AS Destination, [TLDSEU_CUR].BLT_NR,
[TLDSEU_CUR].BAY_NR,RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) AS [Scan Type],
/*( SELECT COUNT(TULO.LD_PKG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '02') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Pkgs,*/
( SELECT COUNT(TULO.LD_BAG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '04') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR) AND
(TLDSEU_CUR.BLT_NR = TULO.BLT_NR) AND (TLDSEU_CUR.BAY_NR = TULO.BAY_NR) ))
AS Bags, COUNT( TOUOWSN_CUR.UOW_TYP_CD
) AS Pkgs, DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(), GetDate() ))
,MAX(TOUOWSN_CUR.PKL_SN_ACT_TS )) AS Last_Local_Scan_Time, ( SELECT TOP 1
SCAN.PKG_TCK_NR FROM TOUOWSN_CUR AS SCAN INNER JOIN TLDSEU_CUR AS TULO ON
SCAN.LD_REF_NR = TULO.LD_REF_NR WHERE( (SCAN.PKL_SN_ACT_TS =
MAX(TOUOWSN_CUR.PKL_SN_ACT_TS) ) AND (SCAN.UOW_TYP_CD = '02') AND
(TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND (TOUOWSN_CUR.SRT_TYP_CD =
SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND
(TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Last_Package,
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,1,4) AS Load_Create_Date FROM TTYPSRT_DCO AS
TTYPSRT_DCO_1 INNER JOIN (TULOLNK_DCO INNER JOIN (TTYPSVC_DCO INNER JOIN
((TOUOWSN_CUR INNER JOIN TTYPSRT_DCO ON TOUOWSN_CUR.SRT_TYP_CD =
TTYPSRT_DCO.SRT_TYP_CD) INNER JOIN TLDSEU_CUR ON TOUOWSN_CUR.LD_REF_NR =
TLDSEU_CUR.LD_REF_NR) ON TTYPSVC_DCO.SVC_TYP_CD = TLDSEU_CUR.SVC_TYP_CD) ON
TULOLNK_DCO.ULO_LNK_SN_CD = TLDSEU_CUR.ULO_LNK_SN_CD) ON
TTYPSRT_DCO_1.SRT_TYP_CD = TLDSEU_CUR.DTN_SRT_TYP_CD WHERE
((([TLDSEU_CUR].ULO_TYP_CD)='04') AND ((TOUOWSN_CUR.UOW_TYP_CD) = '02')
AND ((tldseu_cur.OUB_SRT_DT) = '20071004')) GROUP BY
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) , [TLDSEU_CUR].ULO_NR,
SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO.SRT_TYP_MNM_TE ,
TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE ,
[TLDSEU_CUR].BLT_NR, [TLDSEU_CUR].BAY_NR,
RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) ,
TOUOWSN_CUR.SRT_TYP_CD,TLDSEU_CUR.ORG_OGZ_NR,TLDSEU_CUR.OUB_SRT_DT,TLDSEU_CUR.LD_CRT_DT;
 
R

Roger Carlson

In general, add quote (") marks around each line. End the line with " & _".
And replace every apostrophe (') with two apostophes ('') NOT a quote. So
it would be something like this:

MyString = "SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + ''/'' + " & _
" SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + ''/'' + " & _
...and so forth.

Please note: the marks at the beginning and end of the line are quotes. The
marks around the slashes are TWO apostrophes. I also usually leave a space
at the beginning and ending of each line within the quotes just to be on the
save side.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Mike C said:
I have a fairly long SQL string that I'd like to wrap since I can't fit in
a
single line. I'm not sure how to do it, any help would be greatly
appreciated.

SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date, [TLDSEU_CUR].ULO_NR AS
Trailer, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO.SRT_TYP_MNM_TE AS Origin, TLDSEU_CUR.DTN_CNY_CD + ' ' +
SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO_1.SRT_TYP_MNM_TE
+ '
' + TTYPSVC_DCO.SVC_TYP_MNM_TE AS Destination, [TLDSEU_CUR].BLT_NR,
[TLDSEU_CUR].BAY_NR,RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) AS [Scan Type],
/*( SELECT COUNT(TULO.LD_PKG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '02') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS
Pkgs,*/
( SELECT COUNT(TULO.LD_BAG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '04') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR) AND
(TLDSEU_CUR.BLT_NR = TULO.BLT_NR) AND (TLDSEU_CUR.BAY_NR =
ULO.BAY_NR) ))
AS Bags, COUNT( TOUOWSN_CUR.UOW_TYP_CD
) AS Pkgs, DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(), GetDate() ))
,MAX(TOUOWSN_CUR.PKL_SN_ACT_TS )) AS Last_Local_Scan_Time, ( SELECT TOP 1
SCAN.PKG_TCK_NR FROM TOUOWSN_CUR AS SCAN INNER JOIN TLDSEU_CUR AS TULO ON
SCAN.LD_REF_NR = TULO.LD_REF_NR WHERE( (SCAN.PKL_SN_ACT_TS =
MAX(TOUOWSN_CUR.PKL_SN_ACT_TS) ) AND (SCAN.UOW_TYP_CD = '02') AND
(TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND (TOUOWSN_CUR.SRT_TYP_CD =
SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND
(TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Last_Package,
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,1,4) AS Load_Create_Date FROM TTYPSRT_DCO
AS
TTYPSRT_DCO_1 INNER JOIN (TULOLNK_DCO INNER JOIN (TTYPSVC_DCO INNER JOIN
((TOUOWSN_CUR INNER JOIN TTYPSRT_DCO ON TOUOWSN_CUR.SRT_TYP_CD =
TTYPSRT_DCO.SRT_TYP_CD) INNER JOIN TLDSEU_CUR ON TOUOWSN_CUR.LD_REF_NR =
TLDSEU_CUR.LD_REF_NR) ON TTYPSVC_DCO.SVC_TYP_CD = TLDSEU_CUR.SVC_TYP_CD)
ON
TULOLNK_DCO.ULO_LNK_SN_CD = TLDSEU_CUR.ULO_LNK_SN_CD) ON
TTYPSRT_DCO_1.SRT_TYP_CD = TLDSEU_CUR.DTN_SRT_TYP_CD WHERE
((([TLDSEU_CUR].ULO_TYP_CD)='04') AND ((TOUOWSN_CUR.UOW_TYP_CD) = '02')
AND ((tldseu_cur.OUB_SRT_DT) = '20071004')) GROUP BY
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) , [TLDSEU_CUR].ULO_NR,
SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO.SRT_TYP_MNM_TE ,
TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE ,
[TLDSEU_CUR].BLT_NR, [TLDSEU_CUR].BAY_NR,
RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) ,
TOUOWSN_CUR.SRT_TYP_CD,TLDSEU_CUR.ORG_OGZ_NR,TLDSEU_CUR.OUB_SRT_DT,TLDSEU_CUR.LD_CRT_DT;
Click to expand...
 
V

Van T. Dinh

Roger

Not sure but since you use the double-quote as the "outer" String delimiter
in VBA, I don't think you need to double the single-quotes / apostrophes for
the "inner" String delimiter as they will be treated as normal text, pass it
on to SQL Server (???) which will thren treat the singles / apostrophe as
the String delimiters.

--
Cheers
Van



Roger Carlson said:
In general, add quote (") marks around each line. End the line with " &
_". And replace every apostrophe (') with two apostophes ('') NOT a quote.
So it would be something like this:

MyString = "SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + ''/'' + " & _
" SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + ''/'' + " & _
...and so forth.

Please note: the marks at the beginning and end of the line are quotes.
The marks around the slashes are TWO apostrophes. I also usually leave a
space at the beginning and ending of each line within the quotes just to
be on the save side.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Mike C said:
I have a fairly long SQL string that I'd like to wrap since I can't fit in
a
single line. I'm not sure how to do it, any help would be greatly
appreciated.

SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date, [TLDSEU_CUR].ULO_NR AS
Trailer, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO.SRT_TYP_MNM_TE AS Origin, TLDSEU_CUR.DTN_CNY_CD + ' ' +
SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO_1.SRT_TYP_MNM_TE
+ '
' + TTYPSVC_DCO.SVC_TYP_MNM_TE AS Destination, [TLDSEU_CUR].BLT_NR,
[TLDSEU_CUR].BAY_NR,RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) AS [Scan Type],
/*( SELECT COUNT(TULO.LD_PKG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '02') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS
Pkgs,*/
( SELECT COUNT(TULO.LD_BAG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '04') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR) AND
(TLDSEU_CUR.BLT_NR = TULO.BLT_NR) AND (TLDSEU_CUR.BAY_NR =
LO.BAY_NR) ))
AS Bags, COUNT( TOUOWSN_CUR.UOW_TYP_CD
) AS Pkgs, DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(), GetDate() ))
,MAX(TOUOWSN_CUR.PKL_SN_ACT_TS )) AS Last_Local_Scan_Time, ( SELECT TOP 1
SCAN.PKG_TCK_NR FROM TOUOWSN_CUR AS SCAN INNER JOIN TLDSEU_CUR AS TULO
ON
SCAN.LD_REF_NR = TULO.LD_REF_NR WHERE( (SCAN.PKL_SN_ACT_TS =
MAX(TOUOWSN_CUR.PKL_SN_ACT_TS) ) AND (SCAN.UOW_TYP_CD = '02') AND
(TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND (TOUOWSN_CUR.SRT_TYP_CD =
SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND
(TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Last_Package,
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,1,4) AS Load_Create_Date FROM TTYPSRT_DCO
AS
TTYPSRT_DCO_1 INNER JOIN (TULOLNK_DCO INNER JOIN (TTYPSVC_DCO INNER JOIN
((TOUOWSN_CUR INNER JOIN TTYPSRT_DCO ON TOUOWSN_CUR.SRT_TYP_CD =
TTYPSRT_DCO.SRT_TYP_CD) INNER JOIN TLDSEU_CUR ON TOUOWSN_CUR.LD_REF_NR =
TLDSEU_CUR.LD_REF_NR) ON TTYPSVC_DCO.SVC_TYP_CD = TLDSEU_CUR.SVC_TYP_CD)
ON
TULOLNK_DCO.ULO_LNK_SN_CD = TLDSEU_CUR.ULO_LNK_SN_CD) ON
TTYPSRT_DCO_1.SRT_TYP_CD = TLDSEU_CUR.DTN_SRT_TYP_CD WHERE
((([TLDSEU_CUR].ULO_TYP_CD)='04') AND ((TOUOWSN_CUR.UOW_TYP_CD) =
'02')
AND ((tldseu_cur.OUB_SRT_DT) = '20071004')) GROUP BY
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) , [TLDSEU_CUR].ULO_NR,
SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO.SRT_TYP_MNM_TE ,
TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' '
+
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE ,
[TLDSEU_CUR].BLT_NR, [TLDSEU_CUR].BAY_NR,
RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) ,
TOUOWSN_CUR.SRT_TYP_CD,TLDSEU_CUR.ORG_OGZ_NR,TLDSEU_CUR.OUB_SRT_DT,TLDSEU_CUR.LD_CRT_DT;
Click to expand...
Click to expand...
 
G

Guest

Roger,
I'm getting an error message when I try to paste in the SQL I wrote in
Notepad. It says I have "Too many Line Continuations". Below is the SQL I
made in Notepad.

"SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date, " & _
" [TLDSEU_CUR].ULO_NR AS Trailer, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' '
+ TTYPSRT_DCO.SRT_TYP_MNM_TE AS Origin, " & _
" TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE AS
Destination, " & _
" [TLDSEU_CUR].BLT_NR,
[TLDSEU_CUR].BAY_NR,RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) AS [Scan Type], " & _
" /*( SELECT COUNT(TULO.LD_PKG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR " & _
" WHERE( (SCAN.UOW_TYP_CD = '02') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Pkgs,*/
" & _
" ( SELECT COUNT(TULO.LD_BAG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR " & _
" WHERE( (SCAN.UOW_TYP_CD = '04') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND " & _
" (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR =
TULO.ORG_OGZ_NR) AND (TLDSEU_CUR.BLT_NR = TULO.BLT_NR) AND " & _
" (TLDSEU_CUR.BAY_NR = TULO.BAY_NR) )) AS Bags, COUNT(
TOUOWSN_CUR.UOW_TYP_CD) AS Pkgs, " & _
" DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(), GetDate() ))
,MAX(TOUOWSN_CUR.PKL_SN_ACT_TS )) AS Last_Local_Scan_Time, " & _
" ( SELECT TOP 1 SCAN.PKG_TCK_NR FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR " & _
" WHERE( (SCAN.PKL_SN_ACT_TS = MAX(TOUOWSN_CUR.PKL_SN_ACT_TS) ) AND
(SCAN.UOW_TYP_CD = '02') AND " & _
" (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND (TOUOWSN_CUR.SRT_TYP_CD =
SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND " & _
" (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Last_Package, " & _
" SUBSTRING(TLDSEU_CUR.LD_CRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,1,4) AS Load_Create_Date " & _
" FROM TTYPSRT_DCO AS TTYPSRT_DCO_1 INNER JOIN (TULOLNK_DCO INNER JOIN
(TTYPSVC_DCO INNER JOIN ((TOUOWSN_CUR INNER JOIN " & _
" TTYPSRT_DCO ON TOUOWSN_CUR.SRT_TYP_CD = TTYPSRT_DCO.SRT_TYP_CD) INNER JOIN
TLDSEU_CUR ON " & _
" TOUOWSN_CUR.LD_REF_NR = TLDSEU_CUR.LD_REF_NR) ON TTYPSVC_DCO.SVC_TYP_CD =
TLDSEU_CUR.SVC_TYP_CD) ON " & _
" TULOLNK_DCO.ULO_LNK_SN_CD = TLDSEU_CUR.ULO_LNK_SN_CD) ON
TTYPSRT_DCO_1.SRT_TYP_CD = TLDSEU_CUR.DTN_SRT_TYP_CD " & _
" WHERE ((([TLDSEU_CUR].ULO_TYP_CD)='04') AND ((TOUOWSN_CUR.UOW_TYP_CD) =
'02') AND ((tldseu_cur.OUB_SRT_DT) = '20071004')) " & _
" GROUP BY SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4), " & _
" [TLDSEU_CUR].ULO_NR, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO.SRT_TYP_MNM_TE , " & _
" TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE , " & _
" [TLDSEU_CUR].BLT_NR, [TLDSEU_CUR].BAY_NR,
RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ), " & _
"
TOUOWSN_CUR.SRT_TYP_CD,TLDSEU_CUR.ORG_OGZ_NR,TLDSEU_CUR.OUB_SRT_DT,TLDSEU_CUR.LD_CRT_DT;"

Roger Carlson said:
In general, add quote (") marks around each line. End the line with " & _".
And replace every apostrophe (') with two apostophes ('') NOT a quote. So
it would be something like this:

MyString = "SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + ''/'' + " & _
" SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + ''/'' + " & _
...and so forth.

Please note: the marks at the beginning and end of the line are quotes. The
marks around the slashes are TWO apostrophes. I also usually leave a space
at the beginning and ending of each line within the quotes just to be on the
save side.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Mike C said:
I have a fairly long SQL string that I'd like to wrap since I can't fit in
a
single line. I'm not sure how to do it, any help would be greatly
appreciated.

SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date, [TLDSEU_CUR].ULO_NR AS
Trailer, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO.SRT_TYP_MNM_TE AS Origin, TLDSEU_CUR.DTN_CNY_CD + ' ' +
SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO_1.SRT_TYP_MNM_TE
+ '
' + TTYPSVC_DCO.SVC_TYP_MNM_TE AS Destination, [TLDSEU_CUR].BLT_NR,
[TLDSEU_CUR].BAY_NR,RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) AS [Scan Type],
/*( SELECT COUNT(TULO.LD_PKG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '02') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS
Pkgs,*/
( SELECT COUNT(TULO.LD_BAG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '04') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR) AND
(TLDSEU_CUR.BLT_NR = TULO.BLT_NR) AND (TLDSEU_CUR.BAY_NR =
ULO.BAY_NR) ))
AS Bags, COUNT( TOUOWSN_CUR.UOW_TYP_CD
) AS Pkgs, DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(), GetDate() ))
,MAX(TOUOWSN_CUR.PKL_SN_ACT_TS )) AS Last_Local_Scan_Time, ( SELECT TOP 1
SCAN.PKG_TCK_NR FROM TOUOWSN_CUR AS SCAN INNER JOIN TLDSEU_CUR AS TULO ON
SCAN.LD_REF_NR = TULO.LD_REF_NR WHERE( (SCAN.PKL_SN_ACT_TS =
MAX(TOUOWSN_CUR.PKL_SN_ACT_TS) ) AND (SCAN.UOW_TYP_CD = '02') AND
(TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND (TOUOWSN_CUR.SRT_TYP_CD =
SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND
(TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Last_Package,
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,1,4) AS Load_Create_Date FROM TTYPSRT_DCO
AS
TTYPSRT_DCO_1 INNER JOIN (TULOLNK_DCO INNER JOIN (TTYPSVC_DCO INNER JOIN
((TOUOWSN_CUR INNER JOIN TTYPSRT_DCO ON TOUOWSN_CUR.SRT_TYP_CD =
TTYPSRT_DCO.SRT_TYP_CD) INNER JOIN TLDSEU_CUR ON TOUOWSN_CUR.LD_REF_NR =
TLDSEU_CUR.LD_REF_NR) ON TTYPSVC_DCO.SVC_TYP_CD = TLDSEU_CUR.SVC_TYP_CD)
ON
TULOLNK_DCO.ULO_LNK_SN_CD = TLDSEU_CUR.ULO_LNK_SN_CD) ON
TTYPSRT_DCO_1.SRT_TYP_CD = TLDSEU_CUR.DTN_SRT_TYP_CD WHERE
((([TLDSEU_CUR].ULO_TYP_CD)='04') AND ((TOUOWSN_CUR.UOW_TYP_CD) = '02')
AND ((tldseu_cur.OUB_SRT_DT) = '20071004')) GROUP BY
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) , [TLDSEU_CUR].ULO_NR,
SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO.SRT_TYP_MNM_TE ,
TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE ,
[TLDSEU_CUR].BLT_NR, [TLDSEU_CUR].BAY_NR,
RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) ,
TOUOWSN_CUR.SRT_TYP_CD,TLDSEU_CUR.ORG_OGZ_NR,TLDSEU_CUR.OUB_SRT_DT,TLDSEU_CUR.LD_CRT_DT;
Click to expand...
Click to expand...
 
R

Roger Carlson

I guess I must be missing something. If you're wrappin it in quotes for
VBA, then you must be assigning it to a string variable, but I don't see
that anywhere.

There IS a limit on continuation characters. I'd forgotten about that. So,
I'd replace what I told you before with something like this:
Dim MyString as String

MyString = "SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + ''/'' + "
MyString = MyString & " SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + ''/'' + "
MyString = MyString & " SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date,
"
.... (and so forth)

As Van said earlier (and I think he may be correct), you may not need to
double up the apostrophes. If not, it would be:
Dim MyString as String

MyString = "SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' + "
MyString = MyString & " SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' + "
MyString = MyString & " SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date,
"
.... (and so forth)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Mike C said:
Roger,
I'm getting an error message when I try to paste in the SQL I wrote in
Notepad. It says I have "Too many Line Continuations". Below is the SQL
I
made in Notepad.

"SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date, " & _
" [TLDSEU_CUR].ULO_NR AS Trailer, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + '
'
+ TTYPSRT_DCO.SRT_TYP_MNM_TE AS Origin, " & _
" TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' '
+
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE AS
Destination, " & _
" [TLDSEU_CUR].BLT_NR,
[TLDSEU_CUR].BAY_NR,RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) AS [Scan Type], " &
_
" /*( SELECT COUNT(TULO.LD_PKG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR " & _
" WHERE( (SCAN.UOW_TYP_CD = '02') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS
Pkgs,*/
" & _
" ( SELECT COUNT(TULO.LD_BAG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR " & _
" WHERE( (SCAN.UOW_TYP_CD = '04') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND " & _
" (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR =
TULO.ORG_OGZ_NR) AND (TLDSEU_CUR.BLT_NR = TULO.BLT_NR) AND " & _
" (TLDSEU_CUR.BAY_NR = TULO.BAY_NR) )) AS Bags, COUNT(
TOUOWSN_CUR.UOW_TYP_CD) AS Pkgs, " & _
" DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(), GetDate() ))
,MAX(TOUOWSN_CUR.PKL_SN_ACT_TS )) AS Last_Local_Scan_Time, " & _
" ( SELECT TOP 1 SCAN.PKG_TCK_NR FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR " & _
" WHERE( (SCAN.PKL_SN_ACT_TS = MAX(TOUOWSN_CUR.PKL_SN_ACT_TS) ) AND
(SCAN.UOW_TYP_CD = '02') AND " & _
" (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND (TOUOWSN_CUR.SRT_TYP_CD =
SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND " & _
" (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Last_Package, " & _
" SUBSTRING(TLDSEU_CUR.LD_CRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,1,4) AS Load_Create_Date " & _
" FROM TTYPSRT_DCO AS TTYPSRT_DCO_1 INNER JOIN (TULOLNK_DCO INNER JOIN
(TTYPSVC_DCO INNER JOIN ((TOUOWSN_CUR INNER JOIN " & _
" TTYPSRT_DCO ON TOUOWSN_CUR.SRT_TYP_CD = TTYPSRT_DCO.SRT_TYP_CD) INNER
JOIN
TLDSEU_CUR ON " & _
" TOUOWSN_CUR.LD_REF_NR = TLDSEU_CUR.LD_REF_NR) ON TTYPSVC_DCO.SVC_TYP_CD
=
TLDSEU_CUR.SVC_TYP_CD) ON " & _
" TULOLNK_DCO.ULO_LNK_SN_CD = TLDSEU_CUR.ULO_LNK_SN_CD) ON
TTYPSRT_DCO_1.SRT_TYP_CD = TLDSEU_CUR.DTN_SRT_TYP_CD " & _
" WHERE ((([TLDSEU_CUR].ULO_TYP_CD)='04') AND ((TOUOWSN_CUR.UOW_TYP_CD) =
'02') AND ((tldseu_cur.OUB_SRT_DT) = '20071004')) " & _
" GROUP BY SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4), " & _
" [TLDSEU_CUR].ULO_NR, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO.SRT_TYP_MNM_TE , " & _
" TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' '
+
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE , " & _
" [TLDSEU_CUR].BLT_NR, [TLDSEU_CUR].BAY_NR,
RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ), " & _
"
TOUOWSN_CUR.SRT_TYP_CD,TLDSEU_CUR.ORG_OGZ_NR,TLDSEU_CUR.OUB_SRT_DT,TLDSEU_CUR.LD_CRT_DT;"

Roger Carlson said:
In general, add quote (") marks around each line. End the line with " &
_".
And replace every apostrophe (') with two apostophes ('') NOT a quote.
So
it would be something like this:

MyString = "SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + ''/'' + " & _
" SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + ''/'' + " & _
...and so forth.

Please note: the marks at the beginning and end of the line are quotes.
The
marks around the slashes are TWO apostrophes. I also usually leave a
space
at the beginning and ending of each line within the quotes just to be on
the
save side.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Mike C said:
I have a fairly long SQL string that I'd like to wrap since I can't fit
in
a
single line. I'm not sure how to do it, any help would be greatly
appreciated.

SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date, [TLDSEU_CUR].ULO_NR
AS
Trailer, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO.SRT_TYP_MNM_TE AS Origin, TLDSEU_CUR.DTN_CNY_CD + ' ' +
SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO_1.SRT_TYP_MNM_TE
+ '
' + TTYPSVC_DCO.SVC_TYP_MNM_TE AS Destination, [TLDSEU_CUR].BLT_NR,
[TLDSEU_CUR].BAY_NR,RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1)
+
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) AS [Scan Type],
/*( SELECT COUNT(TULO.LD_PKG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '02') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS
Pkgs,*/
( SELECT COUNT(TULO.LD_BAG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '04') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR) AND
(TLDSEU_CUR.BLT_NR = TULO.BLT_NR) AND (TLDSEU_CUR.BAY_NR =
ULO.BAY_NR) ))
AS Bags, COUNT( TOUOWSN_CUR.UOW_TYP_CD
) AS Pkgs, DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(), GetDate() ))
,MAX(TOUOWSN_CUR.PKL_SN_ACT_TS )) AS Last_Local_Scan_Time, ( SELECT TOP
1
SCAN.PKG_TCK_NR FROM TOUOWSN_CUR AS SCAN INNER JOIN TLDSEU_CUR AS TULO
ON
SCAN.LD_REF_NR = TULO.LD_REF_NR WHERE( (SCAN.PKL_SN_ACT_TS =
MAX(TOUOWSN_CUR.PKL_SN_ACT_TS) ) AND (SCAN.UOW_TYP_CD = '02') AND
(TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND (TOUOWSN_CUR.SRT_TYP_CD =
SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND
(TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Last_Package,
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,1,4) AS Load_Create_Date FROM
TTYPSRT_DCO
AS
TTYPSRT_DCO_1 INNER JOIN (TULOLNK_DCO INNER JOIN (TTYPSVC_DCO INNER
JOIN
((TOUOWSN_CUR INNER JOIN TTYPSRT_DCO ON TOUOWSN_CUR.SRT_TYP_CD =
TTYPSRT_DCO.SRT_TYP_CD) INNER JOIN TLDSEU_CUR ON TOUOWSN_CUR.LD_REF_NR
=
TLDSEU_CUR.LD_REF_NR) ON TTYPSVC_DCO.SVC_TYP_CD =
TLDSEU_CUR.SVC_TYP_CD)
ON
TULOLNK_DCO.ULO_LNK_SN_CD = TLDSEU_CUR.ULO_LNK_SN_CD) ON
TTYPSRT_DCO_1.SRT_TYP_CD = TLDSEU_CUR.DTN_SRT_TYP_CD WHERE
((([TLDSEU_CUR].ULO_TYP_CD)='04') AND ((TOUOWSN_CUR.UOW_TYP_CD) =
'02')
AND ((tldseu_cur.OUB_SRT_DT) = '20071004')) GROUP BY
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) , [TLDSEU_CUR].ULO_NR,
SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO.SRT_TYP_MNM_TE
,
TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + '
' +
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE ,
[TLDSEU_CUR].BLT_NR, [TLDSEU_CUR].BAY_NR,
RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) ,
TOUOWSN_CUR.SRT_TYP_CD,TLDSEU_CUR.ORG_OGZ_NR,TLDSEU_CUR.OUB_SRT_DT,TLDSEU_CUR.LD_CRT_DT;
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Carlson

I think you may be right. Thanks.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Van T. Dinh said:
Roger

Not sure but since you use the double-quote as the "outer" String
delimiter in VBA, I don't think you need to double the single-quotes /
apostrophes for the "inner" String delimiter as they will be treated as
normal text, pass it on to SQL Server (???) which will thren treat the
singles / apostrophe as the String delimiters.

--
Cheers
Van



Roger Carlson said:
In general, add quote (") marks around each line. End the line with " &
_". And replace every apostrophe (') with two apostophes ('') NOT a
quote. So it would be something like this:

MyString = "SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + ''/'' + " & _
" SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + ''/'' + " & _
...and so forth.

Please note: the marks at the beginning and end of the line are quotes.
The marks around the slashes are TWO apostrophes. I also usually leave a
space at the beginning and ending of each line within the quotes just to
be on the save side.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Mike C said:
I have a fairly long SQL string that I'd like to wrap since I can't fit
in a
single line. I'm not sure how to do it, any help would be greatly
appreciated.

SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date, [TLDSEU_CUR].ULO_NR
AS
Trailer, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO.SRT_TYP_MNM_TE AS Origin, TLDSEU_CUR.DTN_CNY_CD + ' ' +
SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + '
' + TTYPSVC_DCO.SVC_TYP_MNM_TE AS Destination, [TLDSEU_CUR].BLT_NR,
[TLDSEU_CUR].BAY_NR,RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) AS [Scan Type],
/*( SELECT COUNT(TULO.LD_PKG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '02') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS
Pkgs,*/
( SELECT COUNT(TULO.LD_BAG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR
WHERE( (SCAN.UOW_TYP_CD = '04') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR) AND
(TLDSEU_CUR.BLT_NR = TULO.BLT_NR) AND (TLDSEU_CUR.BAY_NR =
O.BAY_NR) ))
AS Bags, COUNT( TOUOWSN_CUR.UOW_TYP_CD
) AS Pkgs, DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(), GetDate() ))
,MAX(TOUOWSN_CUR.PKL_SN_ACT_TS )) AS Last_Local_Scan_Time, ( SELECT TOP
1
SCAN.PKG_TCK_NR FROM TOUOWSN_CUR AS SCAN INNER JOIN TLDSEU_CUR AS TULO
ON
SCAN.LD_REF_NR = TULO.LD_REF_NR WHERE( (SCAN.PKL_SN_ACT_TS =
MAX(TOUOWSN_CUR.PKL_SN_ACT_TS) ) AND (SCAN.UOW_TYP_CD = '02') AND
(TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND (TOUOWSN_CUR.SRT_TYP_CD =
SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND
(TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Last_Package,
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,1,4) AS Load_Create_Date FROM TTYPSRT_DCO
AS
TTYPSRT_DCO_1 INNER JOIN (TULOLNK_DCO INNER JOIN (TTYPSVC_DCO INNER JOIN
((TOUOWSN_CUR INNER JOIN TTYPSRT_DCO ON TOUOWSN_CUR.SRT_TYP_CD =
TTYPSRT_DCO.SRT_TYP_CD) INNER JOIN TLDSEU_CUR ON TOUOWSN_CUR.LD_REF_NR =
TLDSEU_CUR.LD_REF_NR) ON TTYPSVC_DCO.SVC_TYP_CD = TLDSEU_CUR.SVC_TYP_CD)
ON
TULOLNK_DCO.ULO_LNK_SN_CD = TLDSEU_CUR.ULO_LNK_SN_CD) ON
TTYPSRT_DCO_1.SRT_TYP_CD = TLDSEU_CUR.DTN_SRT_TYP_CD WHERE
((([TLDSEU_CUR].ULO_TYP_CD)='04') AND ((TOUOWSN_CUR.UOW_TYP_CD) =
'02')
AND ((tldseu_cur.OUB_SRT_DT) = '20071004')) GROUP BY
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) , [TLDSEU_CUR].ULO_NR,
SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' + TTYPSRT_DCO.SRT_TYP_MNM_TE
,
TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' '
+
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE ,
[TLDSEU_CUR].BLT_NR, [TLDSEU_CUR].BAY_NR,
RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) ,
TOUOWSN_CUR.SRT_TYP_CD,TLDSEU_CUR.ORG_OGZ_NR,TLDSEU_CUR.OUB_SRT_DT,TLDSEU_CUR.LD_CRT_DT;
Click to expand...
Click to expand...
Click to expand...
 
V

Van T. Dinh

Each VBA statement can have up to 24 continuation characters (i.e. 25
lines), AFAIK.

--
HTH
Van T. Dinh
MVP (Access)




Mike C said:
Roger,
I'm getting an error message when I try to paste in the SQL I wrote in
Notepad. It says I have "Too many Line Continuations". Below is the SQL
I
made in Notepad.

"SELECT SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4) AS Sort_Date, " & _
" [TLDSEU_CUR].ULO_NR AS Trailer, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + '
'
+ TTYPSRT_DCO.SRT_TYP_MNM_TE AS Origin, " & _
" TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' '
+
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE AS
Destination, " & _
" [TLDSEU_CUR].BLT_NR,
[TLDSEU_CUR].BAY_NR,RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ) AS [Scan Type], " &
_
" /*( SELECT COUNT(TULO.LD_PKG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR " & _
" WHERE( (SCAN.UOW_TYP_CD = '02') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT =
TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS
Pkgs,*/
" & _
" ( SELECT COUNT(TULO.LD_BAG_QY) FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR " & _
" WHERE( (SCAN.UOW_TYP_CD = '04') AND (TULO.ULO_NR =TLDSEU_CUR.ULO_NR)
AND
(TOUOWSN_CUR.SRT_TYP_CD = SCAN.SRT_TYP_CD) AND " & _
" (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND (TLDSEU_CUR.ORG_OGZ_NR =
TULO.ORG_OGZ_NR) AND (TLDSEU_CUR.BLT_NR = TULO.BLT_NR) AND " & _
" (TLDSEU_CUR.BAY_NR = TULO.BAY_NR) )) AS Bags, COUNT(
TOUOWSN_CUR.UOW_TYP_CD) AS Pkgs, " & _
" DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(), GetDate() ))
,MAX(TOUOWSN_CUR.PKL_SN_ACT_TS )) AS Last_Local_Scan_Time, " & _
" ( SELECT TOP 1 SCAN.PKG_TCK_NR FROM TOUOWSN_CUR AS SCAN INNER JOIN
TLDSEU_CUR AS TULO ON SCAN.LD_REF_NR = TULO.LD_REF_NR " & _
" WHERE( (SCAN.PKL_SN_ACT_TS = MAX(TOUOWSN_CUR.PKL_SN_ACT_TS) ) AND
(SCAN.UOW_TYP_CD = '02') AND " & _
" (TULO.ULO_NR =TLDSEU_CUR.ULO_NR) AND (TOUOWSN_CUR.SRT_TYP_CD =
SCAN.SRT_TYP_CD) AND (TLDSEU_CUR.OUB_SRT_DT = TULO.OUB_SRT_DT) AND " & _
" (TLDSEU_CUR.ORG_OGZ_NR = TULO.ORG_OGZ_NR))) AS Last_Package, " & _
" SUBSTRING(TLDSEU_CUR.LD_CRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.LD_CRT_DT,1,4) AS Load_Create_Date " & _
" FROM TTYPSRT_DCO AS TTYPSRT_DCO_1 INNER JOIN (TULOLNK_DCO INNER JOIN
(TTYPSVC_DCO INNER JOIN ((TOUOWSN_CUR INNER JOIN " & _
" TTYPSRT_DCO ON TOUOWSN_CUR.SRT_TYP_CD = TTYPSRT_DCO.SRT_TYP_CD) INNER
JOIN
TLDSEU_CUR ON " & _
" TOUOWSN_CUR.LD_REF_NR = TLDSEU_CUR.LD_REF_NR) ON TTYPSVC_DCO.SVC_TYP_CD
=
TLDSEU_CUR.SVC_TYP_CD) ON " & _
" TULOLNK_DCO.ULO_LNK_SN_CD = TLDSEU_CUR.ULO_LNK_SN_CD) ON
TTYPSRT_DCO_1.SRT_TYP_CD = TLDSEU_CUR.DTN_SRT_TYP_CD " & _
" WHERE ((([TLDSEU_CUR].ULO_TYP_CD)='04') AND ((TOUOWSN_CUR.UOW_TYP_CD) =
'02') AND ((tldseu_cur.OUB_SRT_DT) = '20071004')) " & _
" GROUP BY SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,5,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,7,2) + '/' +
SUBSTRING(TLDSEU_CUR.OUB_SRT_DT,1,4), " & _
" [TLDSEU_CUR].ULO_NR, SUBSTRING(TLDSEU_CUR.ORG_OGZ_NR,2,4) + ' ' +
TTYPSRT_DCO.SRT_TYP_MNM_TE , " & _
" TLDSEU_CUR.DTN_CNY_CD + ' ' + SUBSTRING(TLDSEU_CUR.DTN_OGZ_NR,2,4) + ' '
+
TTYPSRT_DCO_1.SRT_TYP_MNM_TE + ' ' + TTYPSVC_DCO.SVC_TYP_MNM_TE , " & _
" [TLDSEU_CUR].BLT_NR, [TLDSEU_CUR].BAY_NR,
RTRIM(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,1,1) +
LOWER(SUBSTRING(TULOLNK_DCO.ULO_LNK_SN_SHT_TE,2,15)) ), " & _
"
TOUOWSN_CUR.SRT_TYP_CD,TLDSEU_CUR.ORG_OGZ_NR,TLDSEU_CUR.OUB_SRT_DT,TLDSEU_CUR.LD_CRT_DT;"
Click to expand...
 
Top