Revisit Concat String over 255 char from Recordset

[Rx_]

It is the same... but a little different twist.
Comments are in a memo field. Many comments to one customerID (i.e. Wells ID).
The report output is Excel 2010. The customer wants all the comments concat into one large comment that will surpass 255 characters.

The preious post - the CopyFromRecordset or any other Access function limits the data transfer of a recordset to 255 characters. So, a vba function was created. This function allows all the memo fields to be strung together into one large sting variable. And the string can be put into an Excel 2010 cell.

The code worked great. It took several records and added the comment fields together for a string larger than 255 characters.

Problem:
Turns out that some of the individual comments are larger than 255 characters. The output on my form in a text box shows around 285 on some comments. But, the code copies the first 255 then adds square boxes and strange characters. The debug.Print helped me locate some long-winded comment fields. Sure enough, the strange characters start at position 255.

Question:
Any ideas if it is possible to parce an individual field >255 characters and then concat them back together?
Or, is the OpenRecordset part of the limitation?

Function ConcatComments(intPrimaryKeyID As Long) As String ' Wells Primary ID passed in
      Dim dbCommentsCodes   As DAO.Database
      Dim rsCommentsCodes    As DAO.Recordset
      Dim CommentString     As String                      ' Output String
      Dim strCriteria       As String, strsCommentsCodesQL      As String
      ' Change SQL statement to accept intPrimaryKeyID as shown below
10      strsCommentsCodesQL = "SELECT CStr([Date]) & ' : '  & [Comments] & ' |'   AS Comment " & _
                        "FROM Wells INNER JOIN Comments ON Wells.ID_Wells = Comments.ID_Wells " & _
                        "WHERE (((Wells.ID_Wells) = " & intPrimaryKeyID & ")) " & _
                        "ORDER BY Comments.Date DESC , Wells.ID_Wells;"
20    On Error GoTo Err_ConcatComments
30    CommentString = ""
40        Set dbCommentsCodes = CurrentDb
50        Set rsCommentsCodes = dbCommentsCodes.OpenRecordset(strsCommentsCodesQL, dbOpenSnapshot)             ' snapshots run faster
60        With rsCommentsCodes
70            If .RecordCount <> 0 Then
80                Do While Not rsCommentsCodes.EOF
90                    CommentString = CommentString & rsCommentsCodes("Comment") & " "    ' <-- Field to concat and delimiter
                           If Len(rsCommentsCodes("Comment")) > 250 Then
                            Debug.Print intPrimaryKeyID & "   STRING IS " & Len(CommentString) & "  commen is " & Len(rsCommentsCodes("Comment"))
                           End If
100                   .MoveNext
110               Loop
120           End If
130       End With
Exit_ConcatComments:
          'ConcatComments = Null
150       If Not rsCommentsCodes Is Nothing Then
160           rsCommentsCodes.Close
170           Set rsCommentsCodes = Nothing
180       End If
190       Set dbCommentsCodes = Nothing
200   Exit Function
 
Err_ConcatComments:
210        Resume Exit_ConcatComments
 
 End Function

The debug window shows long strings - thought the line feed was just a debug window view issue. Turns out that the String variable is doing something evil.

 

[Rx_]

The problem seems to be a little different.
In the debug window - the strings count goes up and eventually becomes over 1,000.
But, at 255 characters, Access appears to force some kind of line-return.
From there, the string going back to Excel is limited to 255 characters.

I had fooled myself by looking at the immediate window and had not gone back to Excel to look at the data result.

Evidently, the String variable when useing across MS Applicaitons has a 255 character limit?
http://support.microsoft.com/kb/105416

And: Allen Browne also notes this problem.
http://allenbrowne.com/bug-16.html

So now, my question get to be a little more complicated.
Lets say that my concat was suppose to transfer 1,000 characters from 8 variable Acces memo fields over to one Excel cell.
It might be possible with automation on the Access side to create a table with one Memo field - and put the 1,000 characters into it. (see Allen Browne's Union Statement)
From the Memo field to Excel, the 1,000 character string would need to be
transfered over to Excel 250 characters at a time?

MS Office sure put some roadblocks in data transfer.