string in vba hardlines or returns' at 1024 characters

[mackyrm]

I am trying to build an sql statement and put it into a string. I can do this, but at character 1024 the text that follows is put on to a new line, cutting a fieldname in two. I have declared the string but perplexed. Any thoughts?

the section on vba:
mySQL = mySQL + "LEFT JOIN dbo_Contact ON "

translates to the following output, and this fails when the query is run:

LEFT JOIN dbo_Contac
t ON

 

[Uncle Gizmo]

I never do SQL statements like that I just don't like it. Make individual strings for your from, where, left join, right join, order by clause, in a separate text statements and then concatenate them.

 

[mackyrm]

Thanks for the reply, appreciated. Is there any reason why it would do this? Isn't concatenating separate strings into one large string just adding up the characters, and ultimately have the same response in the concatenated string? If not, why would that work when the first methods fails?

 

[gemma-the-husky]

maybe the SQL engine cannot interpret lines longer than 1024 characters. (like notepad)

simply add vbcrlf characters at suitable places.

mySQL = mySQL + vbcrlf & "LEFT JOIN dbo_Contact ON "

 

[mackyrm]

maybe the SQL engine cannot interpret lines longer than 1024 characters. (like notepad)

simply add vbcrlf characters at suitable places.

mySQL = mySQL + vbcrlf & "LEFT JOIN dbo_Contact ON "

Yes, this did the trick, thank for that. Now working.