Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Location
    Colorado, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comma delimited report field (2000)

    I am trying to create a report where each of the fields in each record will likely return only one value except for the last field. Because it returns so many, I would like to have it return all of the values in a list separated by commas, instead of having each create a new line. In other words, instead of the report looking like
    <pre>123456 Green 6/7/07 apple
    banana
    cherry
    24680 Blue 6/7/07 watermellon

    I would like to have it like

    123456 Green 6/7/07 apple, banana, cherry
    24680 Blue 6/7/07 watermellon
    </pre>


    Is there an easy way of doing this? Each record from the query normally creates a new line when the report is created and I am not sure how to get multiple records on one line without using VBA.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comma delimited report field (2000)

    You'll need some VBA. Copy the Concat function from <post:=301,393>post 301,393</post:> to a standard module. You can then use this function to concatenate the values of the last field, either in a query to be used as record source for the report, or in the control source of a text box on the report, e.g.

    =Concat("NameOfTable", "NameOfFieldToBeConcatenated", "ID=" & [ID])

    Substitute the correct names for the table, field to be concatenated and ID field.

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Location
    Colorado, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comma delimited report field (2000)

    Once again, thank you Hans. I knew I could do it with a little VBA, I was just too lazy to do it at the time. This should do the trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •