title: concatenation and max length of string in VBA, access
body: <p>I've had severas problems with strings in access-vba.</p>
  
  <p>The thing is, access (sometimes) limit the string's length to about 255 characters.</p>
  
  <p>However, depending on HOW the string was built, it may be able to grow bigger then 255 chars.</p>
  
  <p>There's an example of WORKING code : </p>
  
  <pre><code>Dim strReq as String
  strReq = "SELECT exampleField1, exampleField2, exampleField3, exampleField4, exampleField5 "
  strReq = strRec &amp; ", exampleField6, exampleField7, exampleField8, .... [etc. insert many fields, you get it]"
  
  strReq = strReq &amp; " FROM myTable INNER JOIN Tbl2 ON ...[many JOINs as well]"
  </code></pre>
  
  <p>And so on, I often work with large queries so the 256 chars is easily busted.</p>
  
  <p>However, these examples doesn't work :</p>
  
  <pre><code>Dim strReq as String
  strReq = "SELECT exampleField1, exampleField2, exampleField3, exampleField4, exampleField5 " &amp; _
  ", exampleField6, exampleField7, exampleField8, .... [etc. insert many fields, you get it]" &amp; _
  " WHERE exampleField1 = x AND  exampleField2 = y AND  exampleField3 = z" &amp; _
  " ORDER BY 1,2,3,4,5,6"
  </code></pre>
  
  <p>And this doesn't work either : </p>
  
  <pre><code>Dim strReq as String
  Dim strWhere as String
  strReq = "SELECT exampleField1, exampleField2, exampleField3, exampleField4, exampleField5 "
  strReq = strRec &amp; ", exampleField6, exampleField7, exampleField8, .... [etc. insert many fields, you get it]"
  strWhere = "WHERE exampleField1 = x "
  strWhere = strWhere &amp; "AND  exampleField2 = y"
  strWhere= strWhere &amp; " AND  exampleField3 = z"
  
  strReq = strReq &amp; strWhere [&amp; strJoin / strOrder / strHaving / etc]
  </code></pre>
  
  <p>I know know aproximatively how I can or cannot concatenate strings but I'd like to know <strong>how strings exactly work on access vba</strong> , because, i'll admit, it seems quite random so far... </p>
  
  <p>*(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 <em>are not the point here</em>)</p>
  
  <p>*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</p>
  
  <pre><code>        strReq = "SELECT " &amp; IIf(Len(rsRap.Fields("top")) &gt; 0, " TOP " &amp; rsRap.Fields("top"), "") &amp; " " &amp; rsRap.Fields("champs") &amp; ", Sum([Canada]*[Quantité]) AS Montant, Sum(TblDetailCom.Quantité) AS Qty " &amp; IIf(Len(rsRap.Fields("rep")) &gt; 0, ", NickName", "")
  
          strReq = strReq &amp; " FROM (SELECT * FROM TblRepresentant WHERE RefRep not In(13,15,26,27,28)) AS TblRepresentant INNER JOIN "
          strReq = strReq &amp; " ((TblProduits LEFT JOIN TblTypBijoux ON TblProduits.Type = TblTypBijoux.IdTypBijoux) "
          strReq = strReq &amp; " INNER JOIN (TblCouleur INNER JOIN ((TblClients INNER JOIN ((TblComm LEFT JOIN RqMaxIdTrait ON TblComm.ID = RqMaxIdTrait.IdCommande) "
          strReq = strReq &amp; " LEFT JOIN TblTraitement ON RqMaxIdTrait.MaxOfIdTrait = TblTraitement.IdTrait) ON TblClients.ID = TblComm.RefClient) "
          strReq = strReq &amp; " INNER JOIN TblDetailCom ON TblComm.ID = TblDetailCom.RefCom) ON TblCouleur.ID = TblDetailCom.RefCoul) "
          strReq = strReq &amp; " ON TblProduits.IdMod = TblDetailCom.RefProd) ON TblRepresentant.RefRep = TblClients.RefRepre "
  
          strReq = strReq &amp; " WHERE (TblClients.RefRepre &lt;&gt; 5 OR (TblClients.RefRepre=5 AND TblClients.ID In (1210,219,189,578))) "
          '(((TblProduits.Coll)=16) AND((TblComm.CoDatCom)&gt;=#2011-01-01# And (TblComm.CoDatCom)&lt;=#2014-01-01#) " 'Params Collection (16) DteDeb/fin
          'strReq = strReq &amp; " AND "
          If Len(rsRap.Fields("type")) &gt; 0 Then
           strReq = strReq &amp; " AND TblProduits.[Type] = " &amp; rsRap.Fields("type")
          End If
  
          If Len(txtDe) &gt; 0 Then
             strReq = strReq &amp; " AND TblTraitement.DtTrait &gt;= #" &amp; txtDe &amp; "# "
          End If
  
          If Len(txtA) &gt; 0 Then
             strReq = strReq &amp; " AND TblTraitement.DtTrait &lt;= #" &amp; txtA &amp; "# "
          End If
  
  
          If Len(rsRap.Fields("pays")) &gt; 0 Then
             strReq = strReq &amp; " AND TblClients.ClPaiePays = '" &amp; rsRap.Fields("pays") &amp; "' "
          End If
          If Len(rsRap.Fields("rep")) &gt; 0 Then
             strReq = strReq &amp; " AND TblClients.RefRepre = " &amp; rsRap.Fields("rep")
          End If
  
          If Len(rsRap.Fields("col")) &gt; 0 Then
           strReq = strReq &amp; " AND TblProduits.Coll=" &amp; rsRap.Fields("col")
          End If
  
  
  
          If Len(rsRap.Fields("group")) &gt; 0 Then
              strReq = strReq &amp; " GROUP BY " &amp; rsRap.Fields("group") &amp; IIf(Len(rsRap.Fields("rep")) &gt; 0, ", NickName", "")
          End If
  
          strReq = strReq &amp; " HAVING Sum([Canada]*[Quantité]) &gt;= 0 "
  
          If Len(rsRap.Fields("order")) &gt; 0 Then
              strReq = strReq &amp; " ORDER BY " &amp; rsRap.Fields("order")
          End If
  </code></pre>