Results 1 to 7 of 7
  1. #1
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136

    Can I combine data from multiple controls in a report in one cell?

    I have a form that has 5 controls (Active_Ingredient_1, Active_Ingredient_2, Active_Ingredient_3, Active_Ingredient_4, Active_Ingredient_5). Is there an easy way to make the data in these controls comma delimited in the report? In other words, I just want one "Active Ingredients" header with all the entries below it in one cell. It can even be hard returns instead of commas....I just want the data in one cell.....Mainly for when the users export the report to Excel.

    Does that make sense?

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That sounds as if you don't have a normalised data structure.

    Each ingredient should be a single record in a table of ingredients linked back to a master record (Recipes? , Bomb Ingredients? Etc.)
    That way whether you have 1 , 10 or 100 ingredients your design copes with it.

    To answer your question you would create a calculated field;

    MyList: Active_Ingredient_1 & ", " & Active_Ingredient_2 & ", " & Active_Ingredient_3 & ", " & Active_Ingredient_4 & ", " & Active_Ingredient_5

    To get clever with it if any ingredient is missing you could utilise the NZ() function.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    matey56 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    136
    Quote Originally Posted by Minty View Post
    That sounds as if you don't have a normalised data structure.

    Each ingredient should be a single record in a table of ingredients linked back to a master record (Recipes? , Bomb Ingredients? Etc.)
    That way whether you have 1 , 10 or 100 ingredients your design copes with it.

    To answer your question you would create a calculated field;

    MyList: Active_Ingredient_1 & ", " & Active_Ingredient_2 & ", " & Active_Ingredient_3 & ", " & Active_Ingredient_4 & ", " & Active_Ingredient_5

    To get clever with it if any ingredient is missing you could utilise the NZ() function.
    I got it to work. I used the following expression

    =[Active Ingredient 1] & " , " & [Active Ingredient 2] & " , " & [Active Ingredient 3] & " , " & [Active Ingredient 4] & " , " & [Active Ingredient 5]

    How do I get rid of the commas where there is no data in the fields?

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can use a interesting property of a null - try this

    ([Active Ingredient 1] + " , ") & ([Active Ingredient 2] + " , ") & ([Active Ingredient 3] + " , ") & ([Active Ingredient 4] + " , ") & [Active Ingredient 5]
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe something like

    IF(Not Isnull(
    [Active Ingredient 1], YourField = [Active Ingredient 1] & ","
    IF(Not Isnull([Active Ingredient 2], YourField = YourField & [Active Ingredient 2] & ","
    IF(Not Isnull([Active Ingredient 3], YourField = YourField & [Active Ingredient 3] & ","
    IF(Not Isnull([Active Ingredient 4], YourField = YourField & [Active Ingredient 4] & ","
    IF(Not Isnull([Active Ingredient 5], YourField = YourField & [Active Ingredient 5]
    IF Right(YourField, 1) = ",", YourField = Left(YourField, Len(YourField) -1)



  6. #6
    Blings's Avatar
    Blings is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    London, UK
    Posts
    125
    That’s wonderful, I’m going to try this later on a Sales Order Acknowledgment report I’m generating after a sales order has been created. Specifically in the page header section where I concatenate the businesses Registration Number and VAT number in one line.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    As Minty mentioned you should have each ingredient as one record? This logic is only going to cause you more problems and workarounds in the future.
    What happens if you need a sixth ingredient?

    If the structure was normalised, I would then use Allen Browne's Concat function. http://allenbrowne.com/func-concat.html

    theDBguy also has his version http://thedbguy.blogspot.com/2016/02/simplecsv.html
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Replies: 8
    Last Post: 11-28-2018, 04:06 PM
  2. Combine Multiple Weeks of Data
    By Rustin788 in forum Queries
    Replies: 1
    Last Post: 09-24-2014, 08:40 AM
  3. Multiple Checkboxes to Populate Data Cell
    By sonoamore in forum Forms
    Replies: 3
    Last Post: 06-02-2012, 09:23 AM
  4. Combine multiple rows into one cell
    By zarfx4 in forum Queries
    Replies: 8
    Last Post: 06-08-2009, 10:42 AM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 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