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

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. — Alex K.
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. — David Zemens
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) — Ludovic Migneault
Do you get an error message when trying to build those long strings? If so, what is that message? — HansUp

HansUp HansUp · Accepted Answer · 2013-07-29T15:27:02

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.

concatenation and max length of string in VBA, access

2 Answers