How concatenate field data

Posted on 2014-07-24
Last Modified: 2014-07-24
I have a form which contains 6 checkboxes.  If for example, checkbox 1 is true then a text field next to it is populated with "Apple".  If checkbox 2 is true then a textbox next to it is populated with "Orange".  If checkbox 3 is true then a textbox next to it is populated with "Banana".  If checkbox 4 is true then a textbox next to it is populated with "Pear".  If checkbox 5 is true then a textbox next to it is populated with "Grapes".  If checkbox 6 is true then a textbox next to it is populated with "Kiwi".  All of the checkboxes and text boxes are in a table that the form is using as its record source.

Then there is a field on a report that I need to have data concatenated into depending on the fields that have data in them based on those checkboxes.  In each case the data would be separated with "OR".  In addition to this there is static text before and after the concatenated string.  The first part would always be "The fruits available are" and the last part is "for you."

So, for example, if checkbox 1 and 3 and 4 were true, then the result in the concatenated field would be:

"The fruits available are Apple OR Banana OR Pear for you."

What would the VBA code be to make this happen on the report?  The trick seems to be getting rid of "OR" if there is no data in a field.
Question by:SteveL13
    LVL 56

    Accepted Solution

    Dim strFruits as string

     strFruits = ""

     If Me.chkBox1 = True then
        strFruits = strFruits "OR Apple"
    End If

     If Me.chkBox2 = True then
        strFruits = strFruits "OR Orange"
    End If

    ' rem and so on
    ' Then

    ' Strip off the first OR
    If strFruits<>"" then
      strFruits = Mid$(strFruits,4)
      strMessage = "The fruits available are " & strFruits & " for you"
      strMessage "No fruits for you"
    End If

    Author Closing Comment

    Absolutely perfect.  Thanks.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now