I have the following query which when returning the results the memo text is always truncated to 255 characters.
INSERT INTO tblSubStatusbyProject ( allcomments, ProjectDataId )
SELECT Last(concatComments([tblStatusComment].[fldProjectDataId], [tblStatusComment].[fldStatusComment])) AS allcomments, Last(tblStatusComment.fldProjectDataId) AS ProjectDataId
GROUP BY tblStatusComment.fldProjectDataId;
As well, the following is the code for the ConcatComments function:
Public Function ConcatComments(strID As Integer, _
strComment As String) As Variant
Static strLastID As Integer
Static strComments As String
If strID = strLastID Then
strComments = strComments & Chr(13) & Chr(10) & strComment
strLastID = strID
strComments = strComment
ConcatComments = strComments
I have also tried Allen Browne's Concat function at http://allenbrowne.com/func-concat.html
with the same results. (The code above is simpler, but does what I need it to do about 10 times faster.)
I have tried this as a regular select query, a create table query and as shown above, an append query into a table with the field pre-defined as memo, and all of them truncate the field.
When debugging the function, the return value of ConcatComments contains the full 255+ character string.
I really need a solution that will return all of these comment records as an aggregated comment for each project. As far as I know, this, or some form of this should work - but can't put a finger on what I'm doing wrong.