Results 1 to 4 of 4
  1. #1
    bethg79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    1

    concatenating data into one text field

    Hello,



    I am making a database for maintaining an inventory of our hazardous waste bottles. The "containers" table has 10 text fields for the user to enter each ingredient, let's call these fields [Name1], [Name2], etc, in the bottle into, and a corresponding field to enter the concentration, lets call those [Conc1], [Conc2], etc. This concentration is a percentage, but they do not enter the % symbol. Some bottles will have all 10 of these filled out, some will only have 1. It depends on what is in the container.

    I have a report that shows the "inventory" of everything in a room - each bottle, including the contents of the bottles.

    On that report, I'm trying to list the contents in the following format:

    [Name1] [Conc1]%, [Name2] [Conc2]%, [Name3] [Conc3]%, . . . [Name10] [Conc10] %

    A simple concatenation, if there are only 2 ingredients, will show:
    Ethanol 20%, Water 80%, %, %, %, %, %, %, %, %

    For now, that is what I'm doing, but obviously what I want it to do is say:
    Ethanol 20%, Water 80%

    Could someone please help me with this? I've been trying to figure it out for several days now, I'm just lost.

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Welcome to the forum !

    Unfortunately you have "Spreadsheeted" you table design, you should have a table that is formatted more like


    ContentsId(AutoNumber PK), WasteBottleID_FK, ConcName, ConcPerc


    Then your query/report outout would be simple to achieve, and if ingredient 11 arrived one day or ingredient 23 it wouldn't matter, as this design of table can accommodate a totally variable number of ingredients.
    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
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you do not seem to be using the data correctly. The Bottles inventory should not have 10 fields each.
    instead , it should only have 2 in a subtable then you can have infinite bottles, not just 10 AND follow relational db rules.

    tblBottles:
    ParentID, BottleID, Name, Conc
    1,1,Ethanol, 20
    1,2,Water, 80
    1,3,glycol, 1

    But to answer your question:
    [Name1] & " " & [Conc1] & "%" & & " " & [Name2] & " " & [Conc2] & "%" & ….


  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Agree with tall the suggestions for a proper database design.

    To add to ranman's suggestion for your current setup I would use something like this to avoid the "%" showing for the empty fields:
    ([Name1] + " " + [Conc1] + "%") & (", " + [Name2] + " " + [Conc2] + "%") & (", " + [Name3] + " " + [Conc3] + "%") &......

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 4
    Last Post: 11-11-2017, 09:43 PM
  2. Replies: 10
    Last Post: 11-18-2014, 04:25 PM
  3. Replies: 4
    Last Post: 10-14-2012, 03:34 AM
  4. Replies: 3
    Last Post: 03-30-2012, 10:50 AM
  5. Concatenating (2x unbound into bound text box)
    By justinwright in forum Programming
    Replies: 1
    Last Post: 08-19-2010, 08:11 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