Learn how to a build a cloud-first strategyRegister Now


How concatenate field data

Posted on 2014-07-24
Medium Priority
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 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 40216487
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

ID: 40216747
Absolutely perfect.  Thanks.

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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