Visible/hidden Yes/No fields in a Report (Access 2010)

[HealdsburgCA]

Greetings,

I’m building a report [rptResoIndex] which contains 8 yes/no fields (check boxes). I started off working to get the report to only show the check boxes that are active/true and hide the check boxes that are not active/false. But as I was working towards this I realized I really don’t need the check boxes, just the labels to show up.

It would be ideal if the report used a text box and showed only the names designated for each field thatis True, while all False fields are hidden. To go one step further it would be even better if when visible the names appeared in the same location on the report (as in using the same textbox, not eight text boxes with the labels appearing to jump around/appear & disappear as the user scrolls through the report).

This seems like a tall order, is it possible for a report to do what I’m hoping?

Table name:

[tblReso_Index]

“Name Designation” and [Field Name]:

“Administration” [DeptAdmin]
“Community Services”[[FONT=Calibri","sans-serif]DeptCommSrvc][/FONT]
[FONT=Calibri","sans-serif]“Fire”[DeptFire][/FONT]
[FONT=Calibri","sans-serif]“Police”[DeptPolice][/FONT]
[FONT=Calibri","sans-serif]“Planning & Building” [DeptPlanBldg][/FONT]
[FONT=Calibri","sans-serif]“Public Works” [DeptPW][/FONT]
[FONT=Calibri","sans-serif]“Electric” [DeptElect][/FONT]
[FONT=Calibri","sans-serif]“Finance”[DeptFin][/FONT]

[FONT=Calibri","sans-serif]Thank you in advance for the help![/FONT]

[FONT=Calibri","sans-serif]-Joshua[/FONT]

 

[AccessMSSQL]

Are the checkboxes mutually exclusive? You could do this in your query if they are:

CheckBoxLabel: iif(checkbox1=true,"text1",iif(checkbox2=true,"text2",iif(checkbox3=true,"text3",iif(checkbox4=true,"text4",iif(checkbox5=true,"text5",iif(checkbox6=true,"text6",iif(checkbox7=true,"text7",iif(checkbox8=true,"text8",""))))))))

Then just show the CheckBoxLabel in your report where you want the text to appear.

 

[HealdsburgCA]

Thank you for the feedback, unfortuantely the fields are not mutually exclusive. At times there will be more than one field checked/designated as "True". I tweaked your expression and put this in a text box:

=iif([DeptAdmin]=true,"Administration",iif([DeptCommSrvc]=true,"Community Service ",iif([DeptElect]=true,"Electric ",iif([DeptFin]=true,"Finance ",iif([DeptFire]=true,"Fire ",iif([DeptPolice]=true,"Police ",iif([DeptPlanBldg]=true,"Planning/Building ",iif([DeptPW]=true,"Public Works ",""))))))))

The expression works... except that as you mentioned it will only label the first field that is true. I'm still a bit new to building expressions, any one with ideas of how to tweak this so it labels more than one field?

Thanks again for the assitance.

-Joshua

 

[HealdsburgCA]

As I mentioned, I'm new to expressions, I pieced it together and this works fine:

=IIf([DeptAdmin]=True,"Administration ") & IIf([DeptCommSrvc]=True,"CommSrvc ") & IIf([DeptElect]=True,"Electri c") & IIf([DeptFin]=True,"Finance ") & IIf([DeptFire]=True,"Fire ") & IIf([DeptPolice]=True,"Police ") & IIf([DeptPlanBldg]=True,"Planning/Bldg ") & IIf([DeptPW]=True,"Public Works")

Thanks again for the help.

-Joshua