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?
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?
-- Tom
Break the string into logical parts and concatenate them at the end
Giving the Part variables meaningful names will help understanding the code at a later date.SQLPart1 = "ABC" SQLPart2 = "DEF" SQLString = SQLPart1 & SQLPart2
sqlConnection = ? sqlFrom = ? sqlNamefield = ? sqlParam1 = ? SQLString = sqlConnection & sqlFrom & sqlNameField & sqlParam1
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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:
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.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
Last edited by tcarnahan; 11-26-2013 at 08:21 AM. Reason: Left out [code]
-- Tom
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
Long AssignmentstrResult = "From ABC _ Where XYZ <3 _ sqlEtc"
Dim sqlWhere As String Dim sqlFrom As String Dim sqlParam As String Dim sqlEtc as String 'assign values sqlResult = sqlFrom & sqlWhere & sqlParam & sqlEtc
Last edited by SamT; 11-28-2013 at 09:30 AM.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ