I try to break the lines at a keyword. I don't like the line continuation method (& _). For me, a long line to too hard to read.
This is what my SQL query in VBA looks like:
Code:
Dim escapedSQL As String
Dim MyVariable As String
Dim SQLA As String
MyVariable = "STUFF"
escapedSQL = "SQL=SELECT+" & Chr(34)
escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "." & Chr(34)
escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
escapedSQL = escapedSQL & "WantedField" & Chr(34) & " s_2, " & Chr(34)
escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "." & Chr(34)
escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
escapedSQL = escapedSQL & "WantedField1" & Chr(34) & " s_3, " & Chr(34)
escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "." & Chr(34)
escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
escapedSQL = escapedSQL & "WantedField2" & Chr(34) & " s_8, " & Chr(34)
escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "." & Chr(34)
escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
escapedSQL = escapedSQL & "TWantedField3" & Chr(34) & " s_18+FROM+" & Chr(34)
escapedSQL = escapedSQL & "DatabaseName" & Chr(34) & "+WHERE+((" & Chr(34)
escapedSQL = escapedSQL & "TableName" & Chr(34) & "." & Chr(34)
escapedSQL = escapedSQL & "WantedField1" & Chr(34) & " IN ("
' Debug.Print escapedSQL
SQLA = ""
SQLA = SQLAccounts & "'" & MyVariable & "', "
SQLA = VBA.Left(SQLAccounts, (Len(SQLA) - 2)) & ")))"
escapedSQL = escapedSQL & SQLA
This makes it easy to comment out a line or to duplicate a line and change part of the line (comment out the original line) as a test.
I had to comment out everything after the Debug statement, but the escapedSQL string looks like
Code:
SQL=SELECT+"DatabaseName"."TableName"."WantedField" s_2, "DatabaseName"."TableName"."WantedField1" s_3, "DatabaseName"."TableName"."WantedField2" s_8, "DatabaseName"."TableName"."TWantedField3" s_18+FROM+"DatabaseName"+WHERE+(("TableName"."WantedField1" IN (