Combining fields in a query

[Angel69]

Hi,

I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it? TIA

 

[pr2-eugin]

Before you carry on, I would suggest you have a look at the bug associated with Memo fields. http://allenbrowne.com/ser-63.html

 

[Angel69]

Thanks pr2-eugin. I'm attempting it in a report instead. I remember in earlier versions of Access creating customer letters and we'd use the 'TRIM" function to get rid of the address2 line if it was empty. I figure I give that a try but it's still giving me a blank. Any suggestions? I'm just adding each memo field control to the report one under the other. TIA

 

[Rx_]

The old truncate 255 character limitation? Yep, it is real.
Wrote code to manage that, might have posted it on this site a year ago.
Basically, parse 250 characters and rebuild into a string. At the end of each string then append the other fields.

My reports all use Excel generated from scratch by vba. No idea if Access Reports supports the memo.

For troubleshooting, my solution was to create a local table for the report.
Start by clearing out the table's data.
Then re-populate the comment field

730       Do While Not rsComments.EOF
740                 rsCommentsReport.Fields("Area") = rsComments.Fields("Area").Value
750                 rsCommentsReport.Fields("Well Name") = rsComments.Fields("Well Name").Value
760                 rsCommentsReport.Fields("Well Status") = rsComments.Fields("Well Status").Value
770                 rsCommentsReport.Fields("User") = rsComments.Fields("LastOfUser ID").Value    ' last user
780                 rsCommentsReport.Fields("Comment") = ConcatComments(rsComments.Fields("ID_Wells").Value)
790    rsCommentsReport.Update
800    rsComments.MoveNext
810    rsCommentsReport.AddNew
820       Loop

The ConcatComments function is the lengthy part that reads a primary ID and puts comments over 255 char back together.

In excel, this allowed the transfer of comments exceeding 2,000 characters to be moved from the local table memo field to and Excel single cell range. Each comment is preceeded by a date, user name followed by the comment (all of this concant together into a huge comment)

The 255 character limitation in Access code is very real.
The assembly of all the comments (with formatting) into a local table with a memo field really worked to my advantage.

 

[Angel69]

I'm not having issues with the memos cutting off. The issue is some memos are blank and I want to list the memos one under the other versus side by side in columns. If the first or second memo are blank it leaves a large gap. How do I hide the blanks?