Results 1 to 4 of 4
  1. #1
    HealthActuary is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    1

    Rich Text field in report ignoring line break from rich text in query

    Hey everyone,

    Little bit of background, I literally graduated from uni three weeks ago, and started my new job immediately. I work as an Actuary in a small firm without any sort of programming or computer specialists. I was working on a giant project involving millions of rows of data in Excel, and asked my immediate boss if they had ever considered using Access. He said yes, but that no one in the company had enough experience, and he invited me to make that my pet project. One thing led to another, and now I am working on switching over all of our administrative processes from Excel into Access, like tracking clients, meetings, tasks, billing, etc. Unfortunately, I have never used Access until this week, so I am flying by the seat of my pants here.

    What I've created so far are three tables: A listing of our clients and their details, a list of our upcoming client meetings (we are a consulting firm), and a list of projects we are working on for each client. What I am trying to do right now for a report is have our meeting notes automatically assembled from the information in the tables. This involves pulling the meeting date and client, finding the corresponding projects, the notes to those projects, etc. I've created a query that works great so far, but the biggest issue is this; most of our clients have multiple projects being presented at each meeting, so this involves pulling in multiple projects into one field. I wanted each product on it's own line, which I managed to do with the ConcatRelated() function that seems to be very common, and simply hitting "enter" in the "delimit by" field. This makes each project appear on a separate line. My problem is that my report completely ignores this, and treats the newline like a space instead. What I can't figure out is how to make those projects appear on independent lines.

    Below I have tried to make some representations of my tables and query:

    tblClients
    ID Client ID Client Name Etc...
    1 005 Name 005
    2 012 Long Name 012

    tblProjects
    ID Client ID Project Meeting Date Etc...
    1 005 Task1 6/3/17
    2 005 Task2 6/3/17
    3 005 Task3 6/3/17
    4 012 Task4 6/5/17
    5 005 Task5 6/10/17
    6 005 Task6 6/10/17

    tblMeetings
    ID Client ID Meeting Date Etc...
    1 005 6/3/7
    2 012 6/5/7
    3 005 6/10/17

    And my Query looks like this:
    Meeting Date Client ID Projects Etc...
    6/3/17 005 Task1
    Task2


    Task3
    6/5/17 012 Task4
    6/10/17 005 Task5
    Task6

    However in my report, the Projects get listed like this:
    Meeting Date Client ID Projects Etc...
    6/3/17 005 Task1 Task2 Task3
    6/5/17 012 Task4
    6/10/17 005 Task5 Task6


    How can I get it to show up in my Report like it does in the query?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The tasks are already individual records which means they are already on separate lines. Why would you need to use ConcatRelated? Just use report design features to prevent the repetitive display of data. Try setting textbox HideDuplicates property to Yes.

    Using a custom function like ConcatRelated can cause slow performance.
    Last edited by June7; 06-04-2017 at 01:04 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    I agree with June7 that there is NO need to combine the record together.

    What I recommend is tapping into the power of Access Sub Reports. You would create a sub rpeort for the notes. This will print all the notes records for the related pararent record.

    TIP: Check out the Northwind Trader template.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, subreports can expand report design capabilities, however, with the example data not sure subreport is called for. Will each task have its own notes?

    If you really must use ConcatRelated(), are you using Allen Browne's code? What do you mean by 'delimit by field'? Do you mean the strSeparator argument? Use CrLf character codes.

    In VBA can use: vbCrLf

    In VBA or query or textbox use: Chr(13) & Chr(10)

    ConcatRelated("field", "table", "criteria", "criteria", Chr(13) & Chr(10))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-06-2019, 12:59 PM
  2. Replies: 4
    Last Post: 08-07-2015, 07:49 AM
  3. Rich text from form to report?
    By Gina Maylone in forum Access
    Replies: 1
    Last Post: 11-26-2014, 11:45 AM
  4. Line Break in Rich Textbox
    By alyon in forum Access
    Replies: 2
    Last Post: 08-15-2011, 01:07 PM
  5. Set format of rich text field
    By silverspr in forum Programming
    Replies: 0
    Last Post: 03-28-2011, 09:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums