VBA中字符串的串联和最大长度，访问

我在 access-vba 中的字符串方面遇到了严重的问题。

问题是，access（有时）将字符串的长度限制为大约 255 个字符。

但是，根据字符串的构建方式，它可能会增长到超过 255 个字符。

有一个工作代码的示例：

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]"

等等，我经常处理大型查询，因此 256 个字符很容易被破坏。

但是，这些示例不起作用：

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"

这也不起作用：

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]

我知道大致知道如何连接字符串，但我想知道字符串在 Access VBA 上到底如何工作，因为，我承认，到目前为止它看起来很随机......

*（请注意，这些字符串的长度应该比 255 个字符更长，并且查询只是作为示例，这些字符串中存在语法错误或确切长度这不是重点)

*编辑 - 添加我实际使用的代码（在工作版本中，尝试了两个错误版本来清理代码，并且两个版本都在错误

        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

您似乎接受了这样一个事实：VBA 字符串can包含超过 255 个字符。作为示例，此代码创建一个 264 个字符的字符串。

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)

相反，您会遇到基于连接字符串的方法的麻烦。我不知道问题到底出在哪里，但我可以告诉您，添加到字符串时可以使用的行延续数量是有限的。例如，以下代码编译并运行没有错误。但是，如果我再添加一个续行（& cstrSegment _），编译器抱怨“连续行过多”.

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

如果这描述了您遇到的问题，则限制是基于行延续，而不是字符串长度。如果需要，您可以通过分多个步骤构建字符串来解决该限制。做"MyBigString = MyBigString & cstrSegment _"直到行延续的限制，然后添加到MyBigString和另外一个"MyBigString = MyBigString & cstrSegment _" block.

确保您不会被您的角色数量所误导see。也许情况是您只看到前 255 个字符，但该字符串实际上包含更多字符。这是有道理的，因为您报告说您没有收到构建字符串显然失败的错误。

确认字符串的实际长度Len():

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

您还可以将字符串的内容打印到“立即”窗口以查看它包含的内容：

Debug.Print MyBigString

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