Report - want to list boolean fields a very specific way

[purpleplatypus]

I have an MS Access table where each record includes, among many other things, 12 boolean fields (checkboxes). I want to build a report which, ideally in ONE text box, displays a string generated as follows:

- First, it displays the contents of a specific text field.
- If *none* of the 12 boolean fields are checked (i.e. none of them are true), then that's it - the string ends at this point.
- If at least one of the boolean fields is checked (true), it gives, in parentheses, a list of short strings corresponding to the true ones.

Examples:
If the text field says "Boogabooga" and none of the boolean fields are checked off, this text box reads:
Boogabooga

If the text field says "Ikiikiptang" and three of the boolean fields are checked off, namely Alpha, Charlie and Foxtrot, this text box reads:
Ikiikiptang (Alpha, Charlie, Foxtrot)

What is the easiest way to accomplish this? I imagine I'd want a query that spits out the parenthetical part, but the exact implementation escapes me.

[zmbd]

Unless you want to go with VBA then you are looking at a series of IIF() conditionals.

A short example and you should be able to go from there...

This example will work as the control source for your text box or as a query, provided the record source for the form or for the query has all of the required Boolean fields.

In case you are not familiar with calculated fields (now I'm going to step this code; however in use, it would be all on a single line)

I'll show you the query version, the control version is basically the same, just use an equal sign instead of the "calcfieldname:"

z_show_flat:
   "ThisIsStatic" &
   IIF([boolean1],"text1","") &
   IIF([boolean2],"text2","") &
   IIF([boolean3],"text3","") &
   IIF([boolean4],"text4","")
(...)

now these are not "nested" so if [Boolean1], [Boolean3], and [Boolean4] are all true then you get: "ThisIsStaticText1Text3Text4" -->NOTE: I ran the text together because this is how it is shown in the code above... if you need spaces, then you will need to add those inline at the correct point within the text-strings or with additional logic.

You would use the [query]![z_show_flat] as the control source for your textbox

Remember, if you are going to use this directly as the textbox control source then REPLACE the "z_show_flat:" with an equal sign "="

I've not proofed this in my test database; however, I've used similar constructs so I'm fairly certain this should work.

[purpleplatypus]

That answers most of my question, and along the lines I was figuring on. However, what I'm not sure of is how to get the parentheses to appear if (and only if) at least one of the boolean fields is true.

[zmbd]

That's going to be in your logic case....
opening IIF() in the conditional would have each [Boolean1]...[BoileanN] "or"d with each other and the same at the end. Thus if any are true then the opening and closing parentheses. Very ugly.
Maybe my SQL Hero Rabbit has a better SQL approach.
The other way is to code a custom function in VBA to look at the form's recordset for the current record and return the resolved string.

[purpleplatypus]

:-(

I'd thought of that but was hoping for something significantly less ugly, like a command I'd overlooked to count the number of "true"s or something. Oh well, back to the salt mines!

Thanks for your help.

[zmbd]

You might be able to use the Switch()Function for the open and close logic. It would return a null value if none of the conditions are true:

z_show_flat: 
   "ThisIsStatic" &
   switch([boolean1],"(",[boolean2],"(",(...)) &
   IIF([boolean1],"text1","") & 
   IIF([boolean2],"text2","") & 
   IIF([boolean3],"text3","") & 
   IIF([boolean4],"text4","") 
   (...) 
   switch([boolean1],")",[boolean2],")",(...)) &
 

This would be a little cleaner than the multiple "OR"; however, even with VBA this is going to be somewhat ugly.

[ADezii]

1. Let's assume that you have a Table named Table1 with the following Fields:
   

   Field1 {TEXT}
   A {YES/NO}
   B {YES/NO}
   C {YES/NO}
   D {YES/NO}
   E {YES/NO}
   F {YES/NO}
   G {YES/NO}
   H {YES/NO}
   I {YES/NO}
   J {YES/NO}

2. Create a Report whose Record Source is Table1 and which consists of only two Text Boxes. One Text Box will be named Field1 and have the same Name for its Control Source.
3. The other Text Box will be Unbound and its Control Source will be:
   

   =fProcessFields([Field1]) 

4. For each Record, the Value of Field1 will be passed to the fProcessFields() Function wherin all the Logic is self contained. For each Record it will then return the appropriate String.
5. The Code has been thoroughly tested and is functional. Don't forget to Copy-N-Paste the Function definition to a Standard Code Module. It is posted below:
   

   Public Function fProcessFields(strField As String)
   Dim MyDB As dao.Database
   Dim rst As dao.Recordset
   Dim intFldCtr As Integer
   Dim blnChecked As Boolean
   Dim strBuild As String
    
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("SELECT * FROM Table1 WHERE [Field1] ='" & strField & "'", dbOpenDynaset)
    
   'First, are NONE of the Boolean Fields Checked? Boolean Fields are 2 thru 11
   For intFldCtr = 1 To 10
     If rst.Fields(intFldCtr) Then
       blnChecked = True   'At least 1 True
         Exit For
     End If
   Next
    
   If Not blnChecked Then   '0 Yes/No Fields are Checked, return Text Field only
     fProcessFields = strField
   Else
     'At least 1 Yes/No Field Checked, reinterate Yes/No Fields & build String
     For intFldCtr = 1 To 10
       If rst.Fields(intFldCtr) Then
         strBuild = strBuild & rst.Fields(intFldCtr).Name & ", "
       End If
     Next
       'The Final Build (Field Name & " (" & strBuild - Trailing ', ' & ")"
       fProcessFields = strField & " (" & Left$(strBuild, Len(strBuild) - 2) & ")"
   End If
    
   Set rst = Nothing
   End Function

6. Sample Return Values from fProcessFields():
   

   Alpha
   Tango (B, D, E, J)
   Charlie (A, B, G, H, I, J)
   Foxtrot (C, F)

7. Any questions, please feel free to ask.