Trying to capture a very long SQL string in a VBA string

[tcarnahan]

I am trying to pass a very long SQL query to a VBA string, but the SQL seems to be getting truncated. Is there a long version of a VBA string?

[SamT]

Break the string into logical parts and concatenate them at the end

SQLPart1 = "ABC"
SQLPart2 = "DEF"

SQLString = SQLPart1 & SQLPart2

Giving the Part variables meaningful names will help understanding the code at a later date.

sqlConnection = ?
sqlFrom = ?
sqlNamefield = ?
sqlParam1 = ?

SQLString = sqlConnection & sqlFrom & sqlNameField & sqlParam1

[tcarnahan]

Thanks Sam!

I am trying to store the SQL in a "template" PT query that I have saved because it has so many parts (unions, pivot, etc.), so what I would have to do is use:

strSql1 = LEFT(qdf.SQL, x)     'where x is the number of characters
strSql2 = MID(qdf.SQL, y,z)    ' where y is "x+1" and z is so many characters
strSql3 = Right(qdf.SQL, LEN(qdf.SQL) - y + z)  

strSqlNEW = strSql1 & strSql2 & strSql3

I thought a "string" data type could take 2 billion characters. Was I mistaken? If it is only 255, then even the code (above) would not work when I concatenate the 3 parts. My SQL is about 3,000 characters.

[SamT]

My main computer is down, so I'm pulling this out of my.. Well you get the picture.

I think that the issue is that a variable Assignment string in VBA has a smaller limit than the String Type. :

So strResult = str1 & str2 & strEtc would have a smaller limit in the number of characters in "str1 & str2 & strEtc" than the length of the actual concatenated String in strResult. Again :

IOW, if str1, str2, and strEtc were each assigned 250 Characters, strResult could hold 750 characters. but the assignment itself, "str1 & str2 & strEtc," is still limited to 255? characters including spaces, ampersands, and Function names and parameters. & Left(str1, 3) = 15 characters. :

I have never run into the problem myself, because I follow the KISASE principle, (Keep It SSimple And Self explanatory Stupid.)

Short String Assignment

strResult = "From ABC _
                  Where XYZ <3 _
sqlEtc"

Long Assignment

Dim sqlWhere As String
Dim sqlFrom As String
Dim sqlParam As String
Dim sqlEtc as String
'assign values

sqlResult = sqlFrom & sqlWhere & sqlParam & sqlEtc