Checkboxes in reports

[sunridge]

Hi everyone,

I am fairly new to Access and have only set up simple databases. The database I am working on now has check boxes. In the Report I only want the check boxes that are checked off to show. How do I do that?

Thanks

[ADezii]

Hi everyone,

I am fairly new to Access and have only set up simple databases. The database I am working on now has check boxes. In the Report I only want the check boxes that are checked off to show. How do I do that?

Thanks

The easiest way would be to modify the criteria in the underlying Query for the Report by setting the Control Source for the Check Box = True

[sunridge]

Thanks, but I don't have a Query for the Report.

[Tetelestai]

You'll need to make a query. Then base the report on the query.


A starter way to make a query is to use the query wizard. You can include all the fields in your table in your query.

Open the query (made with the wizard) in design view, set the critera for the check box field to True

[Rabbit]

I don't know if this will work for a report as I don't use reports much but in the On Render event. Select Event Procedure and put the following in the code.

 CheckboxName.Visible = CheckboxName 

[sunridge]

You'll need to make a query. Then base the report on the query.


A starter way to make a query is to use the query wizard. You can include all the fields in your table in your query.

Open the query (made with the wizard) in design view, set the critera for the check box field to True

I made the query as you said. But when I ran the Query the resulting table came up empty.

[Rabbit]

Use ADezii's solution if you want the entire record to not be shown when the checkbox is unchecked.

Use my solution if you want only the checkbox to not be shown but to still display everything else.

Also, for the query that's coming up empty, what's the SQL?

[sunridge]

I don't know if this will work for a report as I don't use reports much but in the On Render event. Select Event Procedure and put the following in the code.

 CheckboxName.Visible = CheckboxName 

Where is the On Render event?

[sunridge]

Use ADezii's solution if you want the entire record to not be shown when the checkbox is unchecked.

Use my solution if you want only the checkbox to not be shown but to still display everything else.

Also, for the query that's coming up empty, what's the SQL?

I want the entire record to not be shown so I will use ADezii's solution.
I entered True into the Criteria of the checkboxes (in Design View) and then ran the Query. The resulting table was empty. I'm sorry, I don't know what SQL is (I guess I have some learning to do!)

[Rabbit]

Oops, my bad, I ment On Format

In the report design view, right-click the bar that says detail and select properties. Click on the Event tab. The On Format event is located there.

Click the little box with the three dots on the right of the Event Procedure text box. Choose Code Builder from the popup box. Put the code in there. Adjusting for your name of your checkbox of course.

[sunridge]

The easiest way would be to modify the criteria in the underlying Query for the Report by setting the Control Source for the Check Box = True

I'm afraid I'm totally lost. I made a Query using the wizard and then went into Design View. I can't find a Control Source here only Criteria.

[Rabbit]

In the criteria for the checkbox type True.

In the Design View of the query, right click the title bar and select SQL View.
Paste what's in there.

[sunridge]

In the criteria for the checkbox type True.

In the Design View of the query, right click the title bar and select SQL View.
Paste what's in there.

I have several check boxes so I have typed True into the Criteria section of each one. Then I go into SQL View and copy all that's in there? Where do I paste it?

[Rabbit]

You said the query was coming up empty?

Paste it in here so we can take a look at it. Enclose it in Code tags.

[NeoPa]

I think the results of the query come up empty.
The SQL we have yet to see.
Can you also post what was being used as the Record Source of the report before it was changed to the query.
Also, names of items will help with communication on these forums. Otherwise it's easy to get confused between different items (Query; Report; Form etc).

[sunridge]

You said the query was coming up empty?

Paste it in here so we can take a look at it. Enclose it in Code tags.

Here it is:

SELECT [Registration Number],[Address],[Builder],[PO#],
       [Model],[Expected Completion Date],[Registration Date],
       [Heated Ceiling (w/attic)],[Heated Ceiling (w/o attic)],
       [Exterior Walls],[Basement Walls],[Overhanging Floors],
       [Ventilation],[Duct Sealing],[Fireplace],[Windows1],
       [Windows2],[Windows3],[Windows4],[Patio Doors],[Heating],
       [Water Heating],[Air Leakage],[ECM motor in the furnace],
       [ECM motors in HRV],[Compact fluorescent lamps],
       [Fluorescent fixtures],[Motion sensors on lights],
       [Outdoor lighting photoboltaic cells],
       [Energy Star refrigerator],[Energy Star dishwasher],
       [Energy Star clothes washer],[Energy Star AC],
       [Low flush toilets],[Dual flush toilets],
       [Low flow shower heads],[Insulate hot water lines],
       [Front loading clothes washer]
FROM [Builder Option Package]
WHERE ((([ECM motor in the furnace])=True)
  AND (([ECM motors in HRV])=True)
  AND (([Compact fluorescent lamps])=True)
  AND (([Fluorescent fixtures])=True)
  AND (([Motion sensors on lights])=True)
  AND (([Outdoor lighting photoboltaic cells])=True)
  AND (([Energy Star refrigerator])=True)
  AND (([Energy Star dishwasher])=True)
  AND (([Energy Star clothes washer])=True)
  AND (([Energy Star AC])=True)
  AND (([Low flush toilets])=True)
  AND (([Dual flush toilets])=True)
  AND (([Low flow shower heads])=True)
  AND (([Insulate hot water lines])=True)
  AND (([Front loading clothes washer])=True));

[NeoPa]

Your SQL is saying "Show only those records where every boolean item is true." What (exactly) do you want it to say?

[sunridge]

Your SQL is saying "Show only those records where every boolean item is true." What (exactly) do you want it to say?

In the Report I only want the checkboxes that are checked off to show. I don't know how to say that in SQL.

[Rabbit]

That's not what NeoPa meant.

As of right now, your SQL only selects the records where every checkbox is checked. This means that in your report, it will only show those records where every checkbox = True.

Is this what you want?

Or do you only need one checkbox to be checked to show up in the report.

[NeoPa]

In the Report I only want the checkboxes that are checked off to show. I don't know how to say that in SQL.

As Rabbit said :
I don't want you to tell me in SQL. I want you to try to tell me in English. Nothing you've said so far indicates clearly what you want. I can translate into SQL, but only if I can make sense of the English first.
Think about ambiguity. Think about clear references. I'm not sure I know of any better way of expressing the request.

Bear in mind, a record has fields which may be boolean (or Yes/No) but a CheckBox is a control on a Form or Report which is sometimes used to display boolean data.
Sometimes simplified data examples can help to illustrate where the words are not available.

[sunridge]

As Rabbit said :
I don't want you to tell me in SQL. I want you to try to tell me in English. Nothing you've said so far indicates clearly what you want. I can translate into SQL, but only if I can make sense of the English first.
Think about ambiguity. Think about clear references. I'm not sure I know of any better way of expressing the request.

Bear in mind, a record has fields which may be boolean (or Yes/No) but a CheckBox is a control on a Form or Report which is sometimes used to display boolean data.
Sometimes simplified data examples can help to illustrate where the words are not available.

I want to produce a Report from a Table. The table has check boxes. In the report I only want to show the check boxes that are checked off. In other words I want to hide blank check boxes in the report. I was told to make a Query and indicate "True" in the Criteria section of the fields that are check boxes. From there I got totally lost.

[Rabbit]

Ok, then use the code I posted earlier. You indicated earlier that you only wanted records to show if they had checkboxes checked. Which is why they pointed you towards using a query.

So, assuming your data looks like the following:

Name  Chkbox1  Chkbox2
Bob     Yes         No
Tim      No          Yes
Kim     No           No
Eva      Yes         Yes
 
Then the report you want is:
Bob   Chkbox1
Tim                   Chkbox2
Kim
Eva    Chkbox1  Chkbox2
 

Uhh... misaligned but you get the idea.

Is this is what you're looking for, then use my code.

If not, then you'll have to explain more clearly.

[sunridge]

Ok, then use the code I posted earlier. You indicated earlier that you only wanted records to show if they had checkboxes checked. Which is why they pointed you towards using a query.

So, assuming your data looks like the following:

Name  Chkbox1  Chkbox2
Bob     Yes         No
Tim      No          Yes
Kim     No           No
Eva      Yes         Yes
 
Then the report you want is:
Bob   Chkbox1
Tim                   Chkbox2
Kim
Eva    Chkbox1  Chkbox2
 

Uhh... misaligned but you get the idea.

Is this is what you're looking for, then use my code.

If not, then you'll have to explain more clearly.

I found something that works (in a Microsoft Office discussion group). In the Detail section of the Report (in design view) go to Properties and On Format. Put in the following code:
Me.checkbox1.Visible = Me.checkbox1
Me.checkbox2.Visible = Me.checkbox2
Me.checkbox3.Visible = Me.checkbox3

etc. etc.

It seems to work.

Thanks for your help and patience.

[Rabbit]

I found something that works (in a Microsoft Office discussion group). In the Detail section of the Report (in design view) go to Properties and On Format. Put in the following code:
Me.checkbox1.Visible = Me.checkbox1
Me.checkbox2.Visible = Me.checkbox2
Me.checkbox3.Visible = Me.checkbox3

etc. etc.

It seems to work.

Thanks for your help and patience.

Yes, which was exactly what I suggested in the beginning...

[sunridge]

Yes, which was exactly what I suggested in the beginning...

Yes you did. Somehow I got off on another tangent.

Thanks again

[NeoPa]

Well, I'm pleased you found the answer in the end. As Rabbit illustrates with his last post, it is to your own advantage (and simple courtesy) to read (and preferably reply to) all posts in your own thread(s). If there's something you don't understand then we can try to explain more clearly.
The important thing is you found your answer though.