By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,779 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Combining Memo field from multiple records into one field

P: 1
Hi Guys, I am a newbie and need some help.

I have a query that contains a list of questions together with the result per question for a group of learners.
Example: Learner ID; Name, Surname, Question; Result and Comment.

If I run the query on one particular learner, there might be 5 records, each with a comment. I need to combine these comments into one field.
Example: 1st comment followed by 2nd commentfollowed by 3rd comment followed by 4th comment followed by 5th comment.

I need to display the records as well as this one field in a form. Can anyone help?

Thank you.
May 3 '10 #1
Share this Question
Share on Google+
2 Replies

Jim Doherty
Expert 100+
P: 897
I see you have not had a contribution on this for a week.

Some advice do not use NAME as a word for naming fields. It is a reserved word and can cause problems. I notice you mention a query, my assumption is that your data is normalised properly and that your query gathers its source data from separate tables.

To help you, below is a function that expects a numeric 'learnerID' data type of the datatype LONG INTEGER to be passed to it. (If yours is text then it will have to be reflected in the SQL string where the LearnerID is mention)

To get this working open a New Module and paste the function directly into it and save it

The function now becomes available to your query in that you can add it as any other column for instance in the query grid just type in a new column the fieldname as

All Comments: CommentsAll([LearnerID])

and it will return the concatenation of ALL comments but it will display it for EACH row too. This is not so painful to look at if you are simply creating a form that displays a single question record. You can assign place a textbox on the form and set its controlsource to the additional column 'All Comments'

Below is the function

Expand|Select|Wrap|Line Numbers
  1. Function CommentsAll(learnerId As Long) As String
  2. 'loop a column of comments depending on the NUMERIC value of learnerid passed
  3. 'to the function and list the comments per record.
  4. 'but concatenated so that is is a list format suitable of use in a textbox
  5. 'the learnerid is assumed nueric value and that it exists in each row to identify
  6. 'the row.  If you dont want the vbNewline then replace with Space(1)
  7. Dim db As DAO.database
  8. Dim rs As DAO.Recordset
  9. Dim str As String
  10. Dim mysql As String
  11. Set db = CurrentDb
  12. mysql = "SELECT Comment FROM tblLearner WHERE (((tblLearner.LearnerID)=" & learnerId & "));"
  13. Set rs = db.OpenRecordset(mysql, dbOpenDynaset)
  14. If rs.RecordCount <> 0 Then
  15. i = 1
  16.     Do While Not rs.EOF
  17.         str = str & UCase("This is the comment for question (" & i & ")") & vbNewLine
  18.         str = str & Nz(rs!Comment, "(No comment given)") & vbNewLine
  19.         rs.MoveNext
  20.     i = i + 1
  21.     Loop
  22. End If
  23. Debug.Print str
  24. CommentsAll = str
  25. rs.Close
  26. db.Close
  27. Set rs = Nothing
  28. Set db = Nothing
  29. End Function
May 10 '10 #2

Expert Mod 15k+
P: 31,271

Post your reply

Sign in to post your reply or Sign up for a free account.