VBA + String splitting

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.

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

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.

This is what my query looks like

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

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\;"

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.

My coalsce function

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

My query

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)

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).

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.

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

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.

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).

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.

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.