Query, Combining Multiple text lines into one memo field 1

[shirsch]

I am linking to a DB that limits the text fields to 30 characters. If a user post a multi line comment, each comment is separate line item. There are three related fields. The first is the comment id field. Second is the line counter. The third is the comment.

Ex:

CID LID Comment
1 1 Tenant will pay all charges by
1 2 July 31 2006. Tenant will pay
1 3 Sales tax. Spoke To Tj about
1 4 tax issue. TJ will review by
1 5 August 23 05.

What I would like to post all 5 lines into one memo field.
The user can post more than 5 lines in source DB. Can I setup a query to perform this task.

Any suggestion would be greatly appreciated.

 

[GingerR]

Hi. You would write a vba function to do it, then call the function from a query or report. open a new code module and paste this into it. I'm assuming name of the table = "Comments". If it's not, change the fourth line of code to Select * from TableName, whatever your table's name is.

Function BuildComments(CID)

    Dim strComment As String
    strComment = ""
    
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * from Comments where CID = " & CID)
    
    rs.MoveFirst
    
    'Loop thru comments and append them together
    While Not rs.EOF
        If strComment = "" Then
            strComment = rs!Comment
        Else
            strComment = strComment & " " & rs!Comment
        End If
        
        rs.MoveNext
    Wend
    
    'Return string
    BuildComments = strComment
    
    'Close rs
    rs.Close

End Function

From the menu, pick TOOLS+References and make sure that the reference to Microsoft DAO 3.6 is checked.

Then, say you're in a query. You can make a calculated control

Comments: BuildComments(CID)



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[shirsch]

Ginger

Your VB Function worked great! I need to decrease the number of blank spaces after each append. I tried to use Rtrim but results did not change. There are about 3 space between each append. Where can I incorporate Rtrim into the function.

Thanks again for your help.

 

[GingerR]

Hi. It could be because of this line, where I assumed that each comment did not have any spaces before or after them, so I put one in between them:

strComment = strComment & " " & rs!Comment


you can just change it to

strComment = strComment & rs!Comment

and see if that helps.

you can also try this:

strComment = trim(rs!Comment)
  else
strComment = strComment & " " & trim(rs!Comment)

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html