concatenation and max length of string in VBA, access

Question

I've had severas problems with strings in access-vba.

The thing is, access (sometimes) limit the string's length to about 255 characters.

However, depending on HOW the string was built, it may be able to grow bigger then 255 chars.

There's an example of WORKING code :

Dim strReq as String
strReq = "SELECT exampleField1, exampleField2, exampleField3, exampleField4, exampleField5 "
strReq = strRec & ", exampleField6, exampleField7, exampleField8, .... [etc. insert many fields, you get it]"

strReq = strReq & " FROM myTable INNER JOIN Tbl2 ON ...[many JOINs as well]"

And so on, I often work with large queries so the 256 chars is easily busted.

However, these examples doesn't work :

Dim strReq as String
strReq = "SELECT exampleField1, exampleField2, exampleField3, exampleField4, exampleField5 " & _
", exampleField6, exampleField7, exampleField8, .... [etc. insert many fields, you get it]" & _
" WHERE exampleField1 = x AND  exampleField2 = y AND  exampleField3 = z" & _
" ORDER BY 1,2,3,4,5,6"

And this doesn't work either :

Dim strReq as String
Dim strWhere as String
strReq = "SELECT exampleField1, exampleField2, exampleField3, exampleField4, exampleField5 "
strReq = strRec & ", exampleField6, exampleField7, exampleField8, .... [etc. insert many fields, you get it]"
strWhere = "WHERE exampleField1 = x "
strWhere = strWhere & "AND  exampleField2 = y"
strWhere= strWhere & " AND  exampleField3 = z"

strReq = strReq & strWhere [& strJoin / strOrder / strHaving / etc]

I know know aproximatively how I can or cannot concatenate strings but I'd like to know how strings exactly work on access vba , because, i'll admit, it seems quite random so far...

*(Please note, these strings are supposed of longer length then the 255 characters AND the query is just there as an example, syntaxe mistakes or exact length in these are not the point here)

*Edit -- adding the code I'm actually using (With the working version, tried both bugging versions to clean up the code and both were bugging

        strReq = "SELECT " & IIf(Len(rsRap.Fields("top")) > 0, " TOP " & rsRap.Fields("top"), "") & " " & rsRap.Fields("champs") & ", Sum([Canada]*[Quantité]) AS Montant, Sum(TblDetailCom.Quantité) AS Qty " & IIf(Len(rsRap.Fields("rep")) > 0, ", NickName", "")

        strReq = strReq & " FROM (SELECT * FROM TblRepresentant WHERE RefRep not In(13,15,26,27,28)) AS TblRepresentant INNER JOIN "
        strReq = strReq & " ((TblProduits LEFT JOIN TblTypBijoux ON TblProduits.Type = TblTypBijoux.IdTypBijoux) "
        strReq = strReq & " INNER JOIN (TblCouleur INNER JOIN ((TblClients INNER JOIN ((TblComm LEFT JOIN RqMaxIdTrait ON TblComm.ID = RqMaxIdTrait.IdCommande) "
        strReq = strReq & " LEFT JOIN TblTraitement ON RqMaxIdTrait.MaxOfIdTrait = TblTraitement.IdTrait) ON TblClients.ID = TblComm.RefClient) "
        strReq = strReq & " INNER JOIN TblDetailCom ON TblComm.ID = TblDetailCom.RefCom) ON TblCouleur.ID = TblDetailCom.RefCoul) "
        strReq = strReq & " ON TblProduits.IdMod = TblDetailCom.RefProd) ON TblRepresentant.RefRep = TblClients.RefRepre "

        strReq = strReq & " WHERE (TblClients.RefRepre <> 5 OR (TblClients.RefRepre=5 AND TblClients.ID In (1210,219,189,578))) "
        '(((TblProduits.Coll)=16) AND((TblComm.CoDatCom)>=#2011-01-01# And (TblComm.CoDatCom)<=#2014-01-01#) " 'Params Collection (16) DteDeb/fin
        'strReq = strReq & " AND "
        If Len(rsRap.Fields("type")) > 0 Then
         strReq = strReq & " AND TblProduits.[Type] = " & rsRap.Fields("type")
        End If

        If Len(txtDe) > 0 Then
           strReq = strReq & " AND TblTraitement.DtTrait >= #" & txtDe & "# "
        End If

        If Len(txtA) > 0 Then
           strReq = strReq & " AND TblTraitement.DtTrait <= #" & txtA & "# "
        End If


        If Len(rsRap.Fields("pays")) > 0 Then
           strReq = strReq & " AND TblClients.ClPaiePays = '" & rsRap.Fields("pays") & "' "
        End If
        If Len(rsRap.Fields("rep")) > 0 Then
           strReq = strReq & " AND TblClients.RefRepre = " & rsRap.Fields("rep")
        End If

        If Len(rsRap.Fields("col")) > 0 Then
         strReq = strReq & " AND TblProduits.Coll=" & rsRap.Fields("col")
        End If



        If Len(rsRap.Fields("group")) > 0 Then
            strReq = strReq & " GROUP BY " & rsRap.Fields("group") & IIf(Len(rsRap.Fields("rep")) > 0, ", NickName", "")
        End If

        strReq = strReq & " HAVING Sum([Canada]*[Quantité]) >= 0 "

        If Len(rsRap.Fields("order")) > 0 Then
            strReq = strReq & " ORDER BY " & rsRap.Fields("order")
        End If

Liaoning

The limit on a VBA String is around 2GB, why do you thing its limited to 256? - Note that if you view a a string in a message box/watch window it will be displayed truncated. – Lepidolite

String data type has a very large limit around 2 billion characters. There are places where you cannot use strings this long, but you do not get an error when assigning strReq variable. see here for some discussion, e.g., if your table is Text data type, the limit is 255. – Kautz

I'm not talking about the field in a table here. It's about the variable (Dim str... as String) in VBA. I know it "should" be over 255 chars long, however, depending on HOW I concatenate the string i may or may not get longer then 255 characters. (I don't know about the string/varchar field in a table, I'm just asking about the "string" variable used in the forms, in vba) – Liaoning

Do you get an error message when trying to build those long strings? If so, what is that message? – Derron

I get no error message... – Liaoning

Have you done something like Debug.Print Len(strReq) – Derron

I was using addWatch (which works really poorly with strings), I just tried Debug.print and I got a string of 1600 chars (with the working method) I'll try what both other methods gives and I'll update you – Liaoning

If you added watch and read the string from watch, yes you will see just 255 character of the string. However you can see the actual string with Debug.Print – Bromoform

Answer 1

You seem to accept the fact that a VBA string can contain more than 255 characters. As an example this code creates a 264 character string.

Const cstrSegment As String = "0123456789" & vbCrLf
Dim MyBigString As String
Dim i As Long
For i = 1 To 22
    MyBigString = MyBigString & cstrSegment
Next
Debug.Print "Len(MyBigString): " & Len(MyBigString)

Rather you're encountering trouble based on the method you use to concatenate strings. I don't know where that trouble is exactly, but I can tell you there is a limit to the number of line continuations you can use when adding to a string. For example the following code compiles and runs without error. However if I add one more line continuation (& cstrSegment _), the compiler complains "Too many line continuations".

MyBigString = MyBigString & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment _
    & cstrSegment

If that describes the problem you're seeing, the limitation is based on line continuations, not string length. If needed, you could work around that limit by building the string in multiple steps. Do "MyBigString = MyBigString & cstrSegment _" up to the limit of line continuations, then add to MyBigString with another "MyBigString = MyBigString & cstrSegment _" block.

Make sure you're not misled by how many character you see. Perhaps the situation is you're only seeing the first 255 characters, but the string actually contains many more. That would make sense since you reported you're not getting an error building the string apparently fails.

Confirm the actual length of the string with Len():

Debug.Print "Len(MyBigString): " & Len(MyBigString)

You can also print the string's content to the Immediate window to see what it contains:

Debug.Print MyBigString

You can use Ctrl+g to open the Immediate window.

Derron

I know I don't use that much line continuation, it's arround 5 line continuation of 100 characters. Compilation never warned me of "too many line continuation". I can look in my code to give you exactly How many concatenations I'm doing and exactly how long are each lines. Even tho I'm not sure it's quite relevant (5-10 concatenations of 100 chars) sums it well. I'm gonna try something else, maybe every single "cstrSegment" cannot actually excess 256 chars alone. I'm not sure, i'll test it. – Liaoning

OK, my answer was speculative. I think it would help to show us an exact piece of code which causes the problem you're trying to solve. – Derron

No single line actually busted the 110 chars so it wasn't due to a single line longer then 256 chars... However maybe the " & _ " thing makes it look like a single string (so it would bust the 255) and , again, maybe this is causing trouble... Or it isn't ^^' since vba's debug don't show the full string but only the first chars it's hard for me to really test all this... Anyone actually knows why access is doing this? (and how it actually works with srtings)< – Liaoning

-_- I'll be damned... it looks like it works, now... I just have to be showing it to others for the bug to dissapear, as always :/... So ... if I find why it isn't doing it now, and why it was before, I'll post it here. (I think it was because I was putting in [element].controlSource (Which is a query which poppulate a list) – Liaoning

Answer 2

When concatenating strings for SQL, add a vbCrLf character when lines might grow long. Access seems to have trouble ingesting VBA strings (to execute as SQL) greater than about 1000 characters. e.g.

strSQL = strSQL & "SELECT some fields " & vbcrlf & "FROM some table "

Haight