• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

Access 2007 truncating memo fields returned from query

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
FROM tblStatusComment
GROUP BY tblStatusComment.fldProjectDataId;

Open in new window


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
    Else
        strLastID = strID
        strComments = strComment
    End If
    ConcatComments = strComments
    'Debug.Print ConcatComments
End Function

Open in new window


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.
0
Stan_G
Asked:
Stan_G
  • 7
  • 5
  • 2
2 Solutions
 
Stan_GAuthor Commented:
After a little more testing - I can get the aggregate to show up if I remove the Group By / Last, but I still need that functionality.  (I'm working with 20,000+ project records and 750,000+ associated comments which is far too many to be working with, even if dumping to an intermediate table)
0
 
Dale FyeCommented:
Any time you use an aggregate (Group By) clause in your query, memo fields will be truncated.

0
 
Dale FyeCommented:
Get rid of the GroupBy and Last() references and see whether you are getting the full content.  My guess is that you will.  Then figure out a way to identify the "Last" of each of the values without using a Group By clause in your main query (you might be able to do this with a subquery).

Would have to know a little more about your data structure to provide much more help.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Stan_GAuthor Commented:
Yes, you are correct - the first time I removed the Last and Group by I had the same results, but for some reason Access still treated it as an aggregate query.  I created a new query from scratch and it does return the complete content.

Basically here I'm working with a table with the following fields:

fldStatusCommentID (key)
fldProjectDataId
fldStatusComment
fldStatusDate

and I am trying to return all the related comments for each fldProjectDataId , the max of fldStatusCommentId would identify the last comment record - how can I use that in a subquery to return the data I need?
0
 
Nick67Commented:
<Any time you use an aggregate (Group By) clause in your query, memo fields will be truncated.>
As @fyed has stated.

Now, the trick is to save your query without the memo field--since you can't get it in this query anyway.
Since you can use queries as source tables in other queries, you create a new query using the query you just saved as one input table, and the table with the memo field as another.  Get the joins correct, select all the fields from the query, and the memo field from the original table and you should be good to go.

If you need it, I can post a small shim to demonstrate the idea.
0
 
Stan_GAuthor Commented:
@Nick67

That doesn't get around the problem of aggregating all the related memo records into a single result - check my previous comment - I need to concatenate multiple memo records into a single record - which is possible to do with a query.  What I'm having difficulty with is trying to use the group by and last aggregate queries to return only a single result for each projectid.  Unless I missed what you were trying to say, entirely possible as I've been wrestling with this for far too long already.
0
 
Nick67Commented:
The truncation happens because of the Group By.
Your final query must NOT have a Group By in it.
Do the grouping in an interim query and the concatenating in the final query
0
 
Dale FyeCommented:
Modify your concatenation function to something like:

 
Public Function fnConcatComments(intID As Integer) As Variant

    Dim varComments As Variant
    Dim rs as DAO.Recordset
    Dim strSQL as string

    varComments = NULL
    
    strSQL = "SELECT fldStatusComment] FROM tblStatusComment WHERE fldProjectDataId = " & intID
    set rs = currentdb.openrecordset(strSQL,,dbfailonerror)

    While not rs.eof

        varComments = (varComments + vbcrlf + vbcrlf) & rs(0)
        rs.movenext

    Wend

    rs.close
    set rs = nothing

    fnConcatComments = varComments
End Function

Open in new window


Then use the following SQL statement:

SELECT DISTINCT ProjectDataId, fnConcatComments(ProjectDataId) as Comments
FROM tblStatusComment
0
 
Stan_GAuthor Commented:
@Nick67 - Thanks for the extra clarification - that works great!

@fyed - Thanks for the revised function - this also solves my dilemma (along with using a subquery to filter - seems to perform faster than using distinct).

I'll test both of these further to see which provides the best performance - but I'll split the credit between your two solutions.

Thanks tons!!
0
 
Stan_GAuthor Commented:
So out of all this I actually have 3 different solutions to this.  Initial testing looks like fyed's modified function along with a selection statement like the following will provide the best performance for my particular situation.

SELECT fnConcatComments([tblProjectData].[fldProjectDataId]) AS allcomments, ([tblStatusComment].[fldProjectDataId]) AS ProjectDataId, tblStatusComment.fldStatusCommentId
FROM tblProjectData INNER JOIN tblStatusComment ON tblProjectData.fldProjectDataId = tblStatusComment.fldProjectDataId
WHERE (((tblStatusComment.fldStatusCommentId)=(SELECT max([fldStatusCommentId]) FROM [tblStatusComment] where [fldProjectDataId]=[tblProjectData].[fldProjectDataId])));
0
 
Dale FyeCommented:
Now you have me totally confused.

What I thought you said you wanted, was a single record for each ProjectDataID, that contained all of the comments for that ProjectDataID.  The query that you show above will have a record for each ProjectID, CommentID, and the AllComments will be identical for each commentID.  You don't really need the WHERE clause with the modified fnConcatComments() function.  I did realize that the SQL statement I provided probably was not going to work though.  I think that should have been:

SELECT ProjectDataID, fnConcatComments(ProjectDataId) as AllComments
FROM (SELECT DISTINCT ProjectDataId FROM tblStatusComment) as Temp
0
 
Stan_GAuthor Commented:
The WHERE clause is what makes it work by limiting the returned result to the MAX(CommentID) for each project.  The sample I show is extended a little from the original question by adding the JOIN to tblProjectData, but in essence is the same.  I will try the additional FROM (Select) that you suggest above as well.

Thanks again.
0
 
Dale FyeCommented:
I'm still not sure why you bothered to add tblProjectData to the query.  The ProjectDataID is already in tblStatusComment, so the only reason I could possibly see for adding that table to the query would be to include the ProjectName or something like that, which you didn't do.

I just don't understand why you are bothering to include the CommentID in the query, since it only tells you that highest CommentID for a particular ProjectDataID, but the AllComments field contains all of the comments for that ProjectDataID.  If you want that max(CommentID) in the result set, you could do:

SELECT Temp.ProjectDataID, Temp.MaxCommentID, fnConcatComments(ProjectDataId) as AllComments
FROM (SELECT ProjectDataId, Max(CommentID) as MaxCommentID FROM tblStatusComment GROUP BY ProjectDataID) as Temp

And if you want the ProjectName from tblProjectData, you could do:

SELECT Temp.ProjectDataID
           , tblProjectData.ProjectName
           , Temp.MaxCommentID
           , fnConcatComments(ProjectDataId) as AllComments
FROM tblProjectData
INNER JOIN (SELECT ProjectDataId, Max(CommentID) as MaxCommentID
                    FROM tblStatusComment GROUP BY ProjectDataID) as Temp
ON tblProjectData.ProjectDataID = Temp.ProjectDataID
0
 
Stan_GAuthor Commented:
Okay - I finally get what you were trying to say in the last two comments. (I always assume the answer is more complicated than it has to be)

The

SELECT ProjectDataID, fnConcatComments(ProjectDataId) as AllComments
FROM (SELECT DISTINCT ProjectDataId FROM tblStatusComment) as Temp

works exactly as you describe - I kept thinking that I still needed to filter by the fldCommentID, but with the changes you suggested to fnConcatComments, it wasn't necessary, and also allows me to extend the function to include extra fields from tblStatusComment if I want without needing to use any intermediate tables or queries.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now