none
Concatenate fields and stuff results into memo field RRS feed

  • Question

  • I need your guidance on a project.  My data is in SQL tables.  I defined a SQL view to bring together all the data I need on the SQL side.  I have an access query based on the SQL view. 

    Form 1 is a search form used to construct a filter for the query.  The user enters search terms on this form.  The filtering logic is based on this article among others:

    http://allenbrowne.com/ser-62code.html

    Form 2 has check boxes representing table fields.  The user can select which fields they want to report on.

    Form 3 displays the filtered search results.  On the search results form, I give a check box next to each row of the search results.  The user reviews the result set and can check/uncheck the box to select which records to send to the final report.  This sets a flag in each record -- value of 1 = send to report; value of -1 = don't send to report. 

    Currently, the final report is based on the filtered query.  I think instead that I need to dump the query resultset into a table, and report on that.  I need only one table field on the report.  That will be a memo field, and NOT a text box (I have another post in this forum because the text box is not working).  The contents of the memo field will concatenate values from the fields that the user selected.  Assume the source columns are all string values.  Not only do I want to concatenate column values, but I want to add carriage returns/linefeeds between values in the memo field.  (I know how to insert linefeeds in a memo field when entering data interactively -- just press CTRL+ENTER -- but my attempts at inserting control codes aren't working.)

    Further, I want to not leave blank lines in the memo field in case the column data is null.

    Sample data set...

    FirstName    LastName    Title

    -------------------------------------

    John           Doe             Doctor

    Jane           Smith           (null)

    Dennis       (null)            Nurse

    Sample report output -- assumes the user selected all rows and all three fields to report on...

    First Name: John

    Last Name: Doe

    Position: Doctor

    -

    First Name: Jane

    Last Name: Smith

    -

    First Name: Dennis

    Position: Nurse

    (end report)

    Questions in order of importance:

    How do I dump the results of the filtered query into a report table?

    Where do I put the expression that updates the memo field?

    How do I include only the desired fields?

    How do I insert carriage return/line feed between each value within the memo field?

    How do I eliminate nulls when I concatenate values from selected fields?



    • Edited by District9 Wednesday, May 18, 2016 7:13 AM
    Wednesday, May 18, 2016 7:10 AM

Answers

  • 1. To dump the result of the filtered query, you can build a recordset based on it and then populate a temp table with the data.

    2. You can loop through the recordset and update the memo field.

    3. You can use a series of If/Then statements to only include the selected fields.

    4. You can use vbCrLf or Chr(13) & Chr(10) in your code to add a carriage return.

    5. You can use an If/Then statement to skip Null values.

    Hope that helps...

    Wednesday, May 18, 2016 3:10 PM

All replies

  • 1. To dump the result of the filtered query, you can build a recordset based on it and then populate a temp table with the data.

    2. You can loop through the recordset and update the memo field.

    3. You can use a series of If/Then statements to only include the selected fields.

    4. You can use vbCrLf or Chr(13) & Chr(10) in your code to add a carriage return.

    5. You can use an If/Then statement to skip Null values.

    Hope that helps...

    Wednesday, May 18, 2016 3:10 PM
  • DB Guy, that's a good outline.  Thanks.
    Wednesday, May 18, 2016 3:34 PM
  • Good luck! Let us know how it goes.
    Wednesday, May 18, 2016 3:59 PM
  • Hi District9,

    I think the DB guy had give you a proper suggestion that can help you and give some information and guide lines regarding to solve your issue.

    if you think it helpful to you to solve your issue please mark the suggestion as Answer given by the DB guy.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 19, 2016 2:02 AM
    Moderator
  • DB Guy, your solution works, and I learned a lot!  I added a field of type nvarchar(max) to my linked SQL table.  In Access, I set up a DAO recordset based on a filtered query.  I loop through the recordset and update the new (empty) field with concatenations of other fields.  I use IIF to test for null values in the string fields that are to be concatenated.  I add vbCrLf after each concatenated field to format the resulting data in the new field into paragraphs.  Now I have a report with just the one new field which contains paragraphs.  All is good.

    I have one outstanding issue that maybe you can help with (If not, I'll make a separate post to the forum.)  I export the report to RTF.  When the data get's into Word, each line of a paragraphs ends up with a line feed at the end, as opposed to lines that wrap as paragraphs.  (The Access report field is defined as Plain Text.  When I define it as RTF, the field data becomes all one long string, i.e., doesn't respect the embedded vbCrLf's.)  How can I have Word retain the paragraph structure that I see in my Access report field?   Hope you followed that.



    • Edited by District9 Sunday, May 22, 2016 12:33 AM
    Sunday, May 22, 2016 12:31 AM
  • Hi. Glad to hear you got it sorted out. I saw your other post and replied there. Good luck with your project.
    Sunday, May 22, 2016 3:58 AM