Is it better practice to concatenate strings or to use next line?

[Vita]

Is it better practice to concatenate:

string = "Hello"
string = string & " World!"

or to run onto next line

string = "Hello" & _
" World!"

I am thinking its mostly situational leaning torward concatenate.
Would appreciate any other good practices that pop into mind! Either for vba or for general coding!

[Welshgasman]

I tended to do the first. Hardly ever used _

[June7]

They are both concatenating, just one does it in multiple steps and the other does it all in one. Using line continuation is different matter and can also help to make code (string concatenation or otherwise) easier to read, especially when it would extend beyond screen edge.

The multi-step may be easier to read and debug lengthy concatenation, as often happens with building SQL statements.

I think there is limit of 25 on how many line continuations can be in a single block - so the multi-step gets around that for concatenation.

[pbaldy]

I'd say it's personal preference. I've seen a test that showed the second was slightly more efficient, but you'd only see a difference if a lot of concatenation was being done inside a loop that was being executed a large number of times. Realistically there's no performance difference. I usually use the second myself, the first only if I'm doing something between lines that affects the string.

[kd2017]

I go with the first style. I can no longer back this up with evidence but at some point in time the second style caused compiler issues with something I was doing, switching to the first style fixed it. I remember it involving many more lines, at least a dozen or more.

[twgonder]

I've worked with early day BASIC compilers, to pcode. I'm guessing that MS does things in a similar manner with VBA and that they don't do much compiling optimization (they have enough trouble getting the simple things to work to try and tackle that).

If I'm correct in my assumptions (I don't have access to MS code to verify this), the second example would execute much faster, use less memory and possibly require less "garbage collection". My aunt was an admiral and friend of Grace Hopper. I had a CS professor for an assembly class, and he told us about his having to wear her famed wire necklace of shame.

However, overlooking efficiency, June7 is somewhat correct in her assessment as to readability of large strings used for SQL (however, I personally prefer to read one long line even if I have to scroll right; and it's much easier to copy in and out for testing in queries should you need to).

You can't use the "_" in these long strings because you can't split a single variable assignment, in my testing of this.

[June7]

twgonder,

Debug.Print compiled string variable if need to copy/paste to test in query object.

I don't like scrolling right to read code.

I don't really understand your last statement.

[twgonder]

twgonder,
...
I don't really understand your last statement.

Here's an example:

[June7]

Correct, cannot break string by simply terminating with continuation character followed by CR. Have to concatenate with one of the methods in your original post.

[twgonder]

twgonder,

Debug.Print compiled string variable if need to copy/paste to test in query object.

....

That works great for getting SQL out of VBA, getting it into is a whole other story.
Here's a smaller one that I had recently done, and every time I tried to chop it up, I made a mess of it that wouldn't compile.

DefBegSql = "SELECT tbl_3_Tranlt.ID, tbl_3_Tranlt.ClientID, tbl_4_Entity.Nm1, tbl_3_Tranlt.AppModID, tbl_3_Application.DescS, tbl_3_Tranlt.TrTypID, tblu_3_Element.DescS, tbl_3_Tranlt.TrCd, tbl_3_TranltLang.ID, tbl_3_TranltLang.LangID, [tbl_3_TranltLang.LblFrm] & "";"" & [tbl_3_TranltLang.LblRpt] & "";"" & [tbl_3_TranltLang.Hlp] & "";"" & [tbl_3_TranltLang.HlpL] AS LblsMsgHlp FROM tbl_4_Entity INNER JOIN (tblu_3_Element INNER JOIN (tbl_3_Application INNER JOIN (tbl_3_Client INNER JOIN (tbl_3_Tranlt LEFT JOIN tbl_3_TranltLang ON tbl_3_Tranlt.ID = tbl_3_TranltLang.TranltID) ON tbl_3_Client.ID = tbl_3_Tranlt.ClientID) ON tbl_3_Application.ID = tbl_3_Tranlt.AppModID) ON tblu_3_Element.ID = tbl_3_Tranlt.TrTypID) ON tbl_4_Entity.ID = tbl_3_Client.EntityId"

After 30 minutes of this, I said "F-it, just copy the SQL out of a query, fix the single quote to double and get on with it." It's not like I'm going to try to read it and fix that monster within VBA. That's my new rule--works for me.
Besides, I went cross-eyed and got an Advil headache from looking up and down and up and down between the lines.

[June7]

DefBegSql = "SELECT tbl_3_Tranlt.ID, tbl_3_Tranlt.ClientID, tbl_4_Entity.Nm1, tbl_3_Tranlt.AppModID, tbl_3_Application.DescS, " & _
"tbl_3_Tranlt.TrTypID, tblu_3_Element.DescS, tbl_3_Tranlt.TrCd, tbl_3_TranltLang.ID, tbl_3_TranltLang.LangID, " & _
"[tbl_3_TranltLang.LblFrm] & "";"" & [tbl_3_TranltLang.LblRpt] & "";"" & [tbl_3_TranltLang.Hlp] & "";"" & [tbl_3_TranltLang.HlpL] AS LblsMsgHlp " & _
"FROM tbl_4_Entity INNER JOIN (tblu_3_Element INNER JOIN (tbl_3_Application INNER JOIN (tbl_3_Client INNER JOIN (tbl_3_Tranlt " & _
"LEFT JOIN tbl_3_TranltLang ON tbl_3_Tranlt.ID = tbl_3_TranltLang.TranltID) ON tbl_3_Client.ID = tbl_3_Tranlt.ClientID) " & _
"ON tbl_3_Application.ID = tbl_3_Tranlt.AppModID) ON tblu_3_Element.ID = tbl_3_Tranlt.TrTypID) ON tbl_4_Entity.ID = tbl_3_Client.EntityId"

Why change apostrophes to doubled quotes?

[twgonder]

DefBegSql = "SELECT tbl_3_Tranlt.ID, tbl_3_Tranlt.ClientID, tbl_4_Entity.Nm1, tbl_3_Tranlt.AppModID, tbl_3_Application.DescS, " & _
"tbl_3_Tranlt.TrTypID, tblu_3_Element.DescS, tbl_3_Tranlt.TrCd, tbl_3_TranltLang.ID, tbl_3_TranltLang.LangID, " & _
"[tbl_3_TranltLang.LblFrm] & "";"" & [tbl_3_TranltLang.LblRpt] & "";"" & [tbl_3_TranltLang.Hlp] & "";"" & [tbl_3_TranltLang.HlpL] AS LblsMsgHlp " & _
"FROM tbl_4_Entity INNER JOIN (tblu_3_Element INNER JOIN (tbl_3_Application INNER JOIN (tbl_3_Client INNER JOIN (tbl_3_Tranlt " & _
"LEFT JOIN tbl_3_TranltLang ON tbl_3_Tranlt.ID = tbl_3_TranltLang.TranltID) ON tbl_3_Client.ID = tbl_3_Tranlt.ClientID) " & _
"ON tbl_3_Application.ID = tbl_3_Tranlt.AppModID) ON tblu_3_Element.ID = tbl_3_Tranlt.TrTypID) ON tbl_4_Entity.ID = tbl_3_Client.EntityId"

Why change apostrophes to doubled quotes?

The semicolon is inside what was a single double-quote(s). i.e. ";" needs to change to "";"". If I remember and understand your question correctly.
Obviously, you are talented with the keyboard while I am a klutz. (And you probably have better eyesight for those teeny-tiny letters on a laptop.)

[isladogs]

Try this SQL to VBA & Back Again (isladogs.co.uk)

[Vita]

Try this SQL to VBA & Back Again (isladogs.co.uk)

Oh my god this is a life saver if this works. I will definitely try it.

[Vita]

The semicolon is inside what was a single double-quote(s). i.e. ";" needs to change to "";"". If I remember and understand your question correctly.
Obviously, you are talented with the keyboard while I am a klutz. (And you probably have better eyesight for those teeny-tiny letters on a laptop.)

I have had to do this quite a few times manually on this current project and have gotten good at recognizing the mistakes I make and continuing it onto the next line.

What I have gathered from this conversation is that line continuation has a limit of 25 continuations but is more optimized and creates less "garbage collection"(in memory I presume) so it is technically faster but only on code that would be repeating a lot.
While Variable concatenation(I don't know the technical name for this form of concatenation so I just came up with this) can get around this limit.
The readability is whatever the user prefers/finds easier.