VBA +字符串分割 [英] VBA + String splitting

问题描述

我想将< = 1000个字符的结果分割成大小为255的数组，然后使用该值更新数据库中的一个字段.

I want a split a result of <= 1000 characters into an array of size 255 and then update a field in the DB with that.

VBA中的字符串最多支持255个字符，那么我该怎么做呢?

Strings in VBA support a max of 255 characters, so how do I go about doing this?

我使用MS-Access 2007作为数据库，要更新的字段类型为Memo，因此它可以存储255个以上的字符，

I am using MS-Access 2007 as my DB, The field type to be updates is Memo, so it can store more than 255 characters, I have checked this.

这就是我的查询内容

Coalsce(""SELECT (parentdesc & '/' & keyword) FROM All_Keywords_Mapping where item_id=" & rs1![item_id] & """, ""\;"")

Coalsce是我编写的函数，得到的结果如下:

Coalsce is a function that I have written, and it I get a result like:

"abc/123 \; Bcd/123 \; Bcs/sdasdas \; Casad/sdads \; Fea/dasd adsad \; Fea/Zero \; Lo/somer-tet \;"

"abc/123\;Bcd/123\;Bcs/sdasdas\;Casad/sdads\;Fea/dasd adsad\;Fea/Zero\;Lo/somer-tet\;"

现在，我正在将此结果更新为另一个表中的字段和字段数据类型为Memo.当结果超过255个字符时，出现更新查询错误，因此我对其进行了调试，现在检查结果的长度是否小于<. 255然后仅更新，它可以正常工作，但是当超过255时，我无能为力.

now I am updating this result to a field in another table and the field datatype Memo. When the result exceeds 255 chars I get an update query error, so I debugged it and now checking if the length of the result < 255 then only update, it works perfectly fine, but when it exceeds I am unable to do anything.

我的凝聚力功能

Function Coalsce(strSql As String, strDelim As String, ParamArray NameList() As Variant)
Dim db As Database
Dim rs As DAO.Recordset
Dim strList As String

    Set db = CurrentDb

    If strSql <> "" Then
        Set rs = db.OpenRecordset(strSql)

        Do While Not rs.EOF
            strList = strList & rs.Fields(0) & strDelim
            rs.MoveNext
        Loop

        strList = Mid(strList, Len(strDelim))
    Else

        strList = Join(NameList, strDelim)
    End If

    Coalsce = strList

End Function

我的查询

strSql = Select DISTINCT " & rs1![item_id] & " as item_id, FindReplace(Coalsce(""SELECT (desc & '/' & kw) FROM AKM where item_id=" & rs1![item_id] & """, ""\;""), ""/"", ""\/"") as res
Set rs2 = db.OpenRecordset(strSql, dbOpenDynaset)
DoCmd.RunSQL ("Update data_Query set res=" & rs2.Fields("res").Value)

推荐答案

发生这种情况有两个潜在原因.最常见的是您在带有ORDER BY的SQL语句中执行此操作，在这种情况下，备注字段会被截断为255个字符.但是，这似乎不太可能，因为您的函数返回的长度超过255个字符的字符串，因此这不应该成为问题.确实，在有ORDER BY时解决255个字符截断的方法之一是将备忘字段包装在类似Left([MyMemoField], 4096)的函数中.

There are two potential reasons for this to happen. The most common is that you are doing this in a SQL statement with an ORDER BY, in which case memo fields get truncated to 255 characters. However, that doesn't seem likely, as your function is returning the string that's longer than 255 characters, so that shouldn't be an issue. Indeed, one of the ways to get around the 255-character truncation when there's an ORDER BY is to wrap the memo field in a function like Left([MyMemoField], 4096).

另一个原因与备忘录字段的串联有关，在这种情况下，MemoField1 & MemoField2将被截断为255个字符(并且通常以乱码结尾).您没有对源字段的数据类型进行任何说明，因此这似乎不太可能.

The other cause is related to concatenation of memo fields, in which case MemoField1 & MemoField2 will be truncated at 255 characters (and often end in gibberish). You don't say anything about the data types of the source fields, so this seems unlikely.

所以，我基本上是在怀疑您的Coalsce()功能.我的第一个猜测是您定义了一个返回类型:

So, I'm basically suspecting your Coalsce() function. My first guess is that you have defined a return type:

  Public Function Coalsce()

...将返回变量类型，因为您尚未显式设置返回类型.因此，您应该将其更改为:

...that will return a variant type because you haven't explicitly set the return type. So, you should change it to this:

  Public Function Coalsce() As String

但是，有可能您已经决定要在所有字段均为Null时返回Null，在这种情况下，您必须将返回类型声明为Variant:

However, it could be that you've decided you want to return Null when all the fields are Null, in which case, you'd have to declare the return type as Variant:

  Public Function Coalsce() As Variant

在我看来，这是一种不好的做法，因为变体的处理非常复杂.

This seems like bad practice to me, as Variants are complicated to deal with.

这也可能是ByRef/ByVal问题.您真的希望在这种情况下可以通过ByVal处理事情，因为ByRef情况导致连接备忘录字段时出现问题(而ByVal避免了).

It could also be a ByRef/ByVal problem. You really want things to be processed ByVal in this kind of case, as it's the ByRef situation that leads to the problem concatenating memo fields (and ByVal avoids it).

所有这些，我想知道为什么在世界上不能只连接SQL语句中的字段，而不需要一个函数来完成它.

All that said, I wonder why in the world you can't just concatenate the fields in your SQL statement, instead of needing a function to do it.

此外，您存储在备忘字段中的返回数据会引起我的WTF反应-似乎您正在存储重复数据，并且格式几乎无法使用.

Also, the data returned that you're storing in the memo field prompts something of a WTF reaction from me -- it looks like you're storing duplicate data, and in a format that's almost impossible to use.

因此，基本上，如果没有更多关于您正在做的事情，如何做以及为什么做的详细信息，那么任何人都不可能真正回答您的问题.

So, basically, without more details on what you're doing, how you're doing it and why you're doing it, it's impossible for anybody to really answer your question.

这篇关于VBA +字符串分割的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！