vba VBA中字符串的连接和最大长度，访问
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17927042/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
concatenation and max length of string in VBA, access
提问by Ludovic Migneault
I've had severas problems with strings in access-vba.
我在 access-vba 中遇到了字符串问题。
The thing is, access (sometimes) limit the string's length to about 255 characters.
问题是，访问（有时）将字符串的长度限制为大约 255 个字符。
However, depending on HOW the string was built, it may be able to grow bigger then 255 chars.
但是，根据字符串的构建方式，它可能会增长到超过 255 个字符。
There's an example of WORKING code :
有一个 WORKING 代码示例：
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.
依此类推，我经常处理大型查询，因此 256 个字符很容易被破坏。
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...
我大致知道我可以或不能连接字符串，但我想知道字符串在访问 vba 上究竟是如何工作的，因为，我承认，到目前为止它似乎很随机......
*(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)
*（请注意，这些字符串的长度应该比 255 个字符长，并且查询只是作为示例，这些中的语法错误或确切长度不是这里的重点）
*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
回答by HansUp
You seem to accept the fact that a VBA string cancontain more than 255 characters. As an example this code creates a 264 character string.
您似乎接受了 VBA 字符串可以包含超过 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)
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".
而是根据用于连接字符串的方法遇到问题。我不知道问题究竟出在哪里，但我可以告诉您，在添加到字符串时可以使用的行延续数是有限制的。例如，下面的代码编译和运行没有错误。但是，如果我再添加一行延续 ( & 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
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 MyBigStringwith another "MyBigString = MyBigString & cstrSegment _"block.
如果这描述了您所看到的问题，则限制基于行延续，而不是字符串长度。如果需要，您可以通过分多个步骤构建字符串来解决该限制。做"MyBigString = MyBigString & cstrSegment _"达线延续的极限，再加入到MyBigString另一个"MyBigString = MyBigString & cstrSegment _"块。
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.
确保您没有被看到的字符数量所误导。也许情况是您只看到前 255 个字符，但字符串实际上包含更多字符。这是有道理的，因为您报告说您没有收到错误，构建字符串显然失败了。
Confirm the actual length of the string with Len():
使用以下命令确认字符串的实际长度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+gto open the Immediate window.
您可以使用Ctrl+g打开立即窗口。
回答by user10887287
When concatenating strings for SQL, add a vbCrLfcharacter when lines might grow long. Access seems to have trouble ingesting VBA strings (to execute as SQL) greater than about 1000 characters. e.g.
为 SQL 连接字符串vbCrLf时，当行可能变长时添加一个字符。Access 似乎在摄取大于 1000 个字符的 VBA 字符串（作为 SQL 执行）时遇到问题。例如
strSQL = strSQL & "SELECT some fields " & vbcrlf & "FROM some table "
