级联和字符串在VBA最大长度，访问 [英] concatenation and max length of string in VBA, access

查看：399
本文介绍了级联和字符串在VBA最大长度，访问的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我已经severas问题，在访问VBA字符串。

的事情是，进入（有时）限制字符串的长度约255个字符。

不过，这取决于如何串建成，它可能是能够成长更大然后255个字符。

有工作code的例子：

 暗淡strReq为String
strReq =SELECT exampleField1，exampleField2，exampleField3，exampleField4，exampleField5
strReq = STRREC和放大器; exampleField6，exampleField7，exampleField8，... [插入等诸多领域，你得到它们。

strReq = strReq和放大器; FROM mytable的INNER JOIN TBL2开... [很多连接以及]

等等，我经常与大的查询，以256个字符是很容易被淘汰。

然而，这些例子不工作：

 暗淡strReq为String
strReq =SELECT exampleField1，exampleField2，exampleField3，exampleField4，exampleField5＆放大器; _
exampleField6，exampleField7，exampleField8，... [插入等诸多领域，你得到它]＆放大器; _
WHE​​RE exampleField1 = X和exampleField2 = y和exampleField3 = Z和放大器; _
ORDER BY 1,2,3,4,5,6

这也不行：

 暗淡strReq为String
昏暗strWhere为String
strReq =SELECT exampleField1，exampleField2，exampleField3，exampleField4，exampleField5
strReq = STRREC和放大器; exampleField6，exampleField7，exampleField8，... [插入等诸多领域，你得到它们。
strWhere =WHERE exampleField1 = X
strWhere = strWhere和放大器; 与exampleField2 = Y
strWhere = strWhere和放大器; 与exampleField3 = Z

strReq = strReq和放大器; strWhere [＆放大器; strJoin / strOrder / strHaving的/ etc]

我知道知道aproximatively我怎么能或者不能连接字符串，但我想知道的如何将字符串完全适用于Access VBA ，因为，我得承认，它似乎很随意至今。 ..

*（请注意，这些字符串应该再长则255个字符，查询只是有作为一个例子，syntaxe错误或确切长度在这些的不是这里的关键）

*编辑 - 添加code，我实际使用（与工作版本，都尝试窃听版本清理code和两人都烦

  strReq =SELECT＆放大器; IIF（莱恩（rsRap.Fields（顶部））大于0，TOP＆安培; rsRap.Fields（顶部），）及与＆amp; rsRap.Fields（冠军）及，SUM（[加拿大] * [Quantité]）作为Montant，SUM（TblDetailCom.Quantité）AS数量和放大器; IIF（LEN（rsRap.Fields（代表））大于0，绰号，）

        strReq = strReq和放大器; FROM（SELECT * FROM TblRe presentant WHERE RefRep不是在（13,15,26,27,28））AS TblRe presentant 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和放大器; 开TblProduits.IdMod = TblDetailCom.RefProd）ON TblRe presentant.RefRep = TblClients.RefRe pre

        strReq = strReq和放大器; WHE​​RE（TblClients.RefRe pre＆其中;→5或（TblClients.RefRe pre值= 5与TblClients.ID在（1210,219,189,578）））
        （（（TblProduits.Coll）= 16）和（（TblComm.CoDatCom）＆GT; =＃2011-01-01＃和（TblComm.CoDatCom）＆LT; =＃2014年1月1日＃）'PARAMS集（ 16）DteDeb / FIN
        strReq = strReq和放大器;  和 
        如果len（rsRap.Fields（类））＆GT; 0然后
         strReq = strReq和放大器; 与TblProduits [类型] =＆放大器; rsRap.Fields（类型）
        结束如果

        如果len（txtDe）＆GT; 0然后
           strReq = strReq和放大器; 与TblTraitement.DtTrait＆GT; =＃与＆amp; txtDe和放大器; ＃
        结束如果

        如果len（txtA）＆GT; 0然后
           strReq = strReq和放大器; 与TblTraitement.DtTrait＆其中; =＃与＆amp; txtA和放大器; ＃
        结束如果


        如果len（rsRap.Fields（支付））＆GT; 0然后
           strReq = strReq和放大器; 与TblClients.ClPaiePays ='与＆amp; rsRap.Fields（支付）及'
        结束如果
        如果len（rsRap.Fields（代表））＆GT; 0然后
           strReq = strReq和放大器; 与TblClients.RefRe pre =＆放大器; rsRap.Fields（代表）
        结束如果

        如果len（rsRap.Fields（COL））＆GT; 0然后
         strReq = strReq和放大器; 与TblProduits.Coll =与＆amp; rsRap.Fields（COL）
        结束如果



        如果len（rsRap.Fields（「本集团」））＆GT; 0然后
            strReq = strReq和放大器; GROUP BY＆放大器; rsRap.Fields（「本集团」）及IIF（LEN（rsRap.Fields（代表））大于0，绰号，）
        结束如果

        strReq = strReq和放大器; 说完总和（[加拿大] * [Quantité]）＆GT; = 0

        如果len（rsRap.Fields（订单））＆GT; 0然后
            strReq = strReq和放大器; ORDER BY＆放大器; rsRap.Fields（订单）
        结束如果

解决方案

您似乎接受一个事实，即VBA字符串的可以的包含超过255个字符。作为一个例子这种code创建了一个264字符的字符串。

常量cstrSegment的String =0123456789和放大器; vbCrLf 昏暗的MyBigSt​​ring作为字符串 昏暗我只要 对于i = 1到22     MyBigSt​​ring = MyBigSt​​ring和放大器; cstrSegment 下一个 Debug.Print莱恩（MyBigSt​​ring）：＆放大器;莱恩（MyBigSt​​ring）

而你遇到的基础上使用连接字符串的方法麻烦。我不知道在哪里的问题是完全的，但我可以告诉你，是有一定限度的行延续，您可以使用添加到字符串时数。例如下面的code编译和运行没有错误。但是如果我增加一个续行（＆放大器; cstrSegment _ ），编译器会抱怨的有太多的行延续的

MyBigSt​​ring = MyBigSt​​ring和放大器; 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

如果描述你所看到的问题，限制是基于行的延续，而不是字符串的长度。如果需要，你可以解决这一限制通过构建字符串中的多个步骤。做MyBigSt​​ring = MyBigSt​​ring和放大器; cstrSegment _高达行延续了极限，然后添加到 MyBigSt​​ring 与另一MyBigSt​​ring = MyBigSt​​ring和放大器; cstrSegment _。块

请确保你不是你有多少字符的误导见的。也许情况是你只看到前255个字符，但字符串实际上包含了许多。这将使意义，因为你报你没有得到一个错误建筑显然失败的字符串。

确认字符串与的实际长度莱恩（）：

Debug.Print莱恩（MyBigSt​​ring）：＆放大器;莱恩（MyBigSt​​ring）

您还可以打印字符串的内容立即窗口，看它所包含的内容：

Debug.Print MyBigSt​​ring

您可以使用<大骨节病>控制 + <大骨节病>先按g 打开立即窗口。

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

解决方案

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.

这篇关于级联和字符串在VBA最大长度，访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Access最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆