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

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?

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

Code:

---

SQLPart1 = "ABC"
SQLPart2 = "DEF"

SQLString = SQLPart1 & SQLPart2

---

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

Code:

---

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

SQLString = sqlConnection & sqlFrom & sqlNameField & sqlParam1

---

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:

Code:

---

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.

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. :dunno:

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 :dunno:

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. :dunno:

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

Short String Assignment

Code:

---

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

---

Long Assignment

Code:

---

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

sqlResult = sqlFrom & sqlWhere & sqlParam & sqlEtc

---