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

Answer 1

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 "

Answer 2

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.