Report where check box displayed only when true

[Melee]

Database has a bunch of check boxes -- I want a report that displays the list of only those that are true for a given record.
Say there are twenty-five check boxes. Of those thirteen have a value of true. The report should not display those that are false and should have a nice compact list of those that are true.
Any suggestions?

 

[kathryn]

It depends on how your table is set up. Is it set up like this:

ID Checkbox1 Checkbox2 Checkbox3 .... CheckBox25
-- --------- --------- --------- ----------
1 T F T T

Or like this:

ID CheckBoxNumber Checkbox
1 1 T
1 2 F
1 3 T
.
.
.
1 25 T


If it is set up the second way, what you want is going to be much easier. In my MS Access training classes I always tell students that if they have fields with numbers, such as XYZ1, XYZ2, XYZ3, that that should be a red flag to them.

Let me know how you are set up and we can go from there.

 

[Melee]

Kathryn,

The first example is the set up. (I never do things the easy way!)

ID Check1 Check2 Check3 .........
99 T F F .........

 

[kathryn]

OK, it can be done, but it won't be pretty. Could you tell me exactly what you want the report to look like?

Something like

99 Checkbox1,CheckBox4,....

or

99
CheckBox1
CheckBox4
etc.

 

[Melee]

All I want is a subreport that lists the instance where the condition is true for a given company. Nothing fancy.

Company ID
CheckLabel1,CheckLabel2,CheckLabel3, CheckLabel4, CheckLabel5, CheckLabel6, CheckLabel7, CheckLabel8, CheckLabel9, .............

 

[kathryn]

OK, you want them concatenated into one line.

Open a new query, and paste the followinging into your SQL view.

SELECT CompanyID, IIf([CheckLabel1]=-1,"CheckLabel1",Null) AS 1, IIf([CheckLabel2]=-1,"CheckLabel2",Null) AS 2, IIf([CheckLabel3]=-1,"CheckLabel3",Null) AS 3, IIf([CheckLabel4]=-1,"CheckLabel4",Null) AS 4, [1] & [2] & [3] & [4] AS Checked
FROM Table1;

Change Table1 to your table's name. I assumed that you have all of these fields in one table. I used the field names that you gave above. See if this is what you are aiming for. If so, you can add the other 21 fields, and base your subreport on this.

You will need to add commas in Checked. It is easiest to add them to the IIf statements. Change "CheckLabel1" to "CheckLabel1, ".

Let me know how that goes.
[sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[Melee]

After a week of chaos, I should be able to get back to this issue today. Thanks for your assistance, I will let you know how it goes.

Lee [sig][/sig]