Tick Box Query. 2

[brent01]

I have a form that contains 7 tick boxes for days of the week Mon - Sun. The form contains information of batch jobs and what days that they run on. So a tick box is selected for aby relevant days.

I want a report to show the job name along with the days it runs according to the tick box selection made in the form.

Using the report wizard I am getting field labels (Monday, Tuesday etc.) but under the corresponding text boxes are just showing '-1' for ticked days and blank for unticked days.

How can I get a report to show Monday, Tuesday etc in the text boxes if a tick box was selected in the form. Also any un-ticked boxes I would like to be hidden.

I hope this makes sense.

Can anybody help please?

 

[lupins46]

For each textbox which is currently showing the -1 or 0 you need to change the controlsource.

for example the Monday box should be:

=IIf(nameoftheMondaycheckboxfieldhere =true, "Monday","")

where you put the name of your field where indicated.

 

[BobStubbs]

I use this technique to obtain a similar effect. I want check boxes to appear in a report as 'YES' if the check box is ticked (true) and 'NO' if the box is not ticked (false).

In the query which provides data for the report, I create a calculated column corresponding to each check box, like this:

txtMonday: IIf(tblMyTable.chkMonday=True,"Yes","No")

This example creates a calculated column called txtMonday.
This contains the value 'Yes' if the check box 'chkMonday' is checked, and 'No' if it is unchecked.

txtMonday can then be included as a field in the report.

In your case you would create seven such columns, one for each check box, and use a formula like this:

txtMonday: IIf(tblMyTable.chkMonday=True,"Monday","")

This will display the day of the week, or be blank, depending on the check box status.


If your report runs directly from a table, instead of via a query, you can use the same technique:

-- Create an unbound text box txtMonday on the report.
-- Include the 'Monday check box' (e.g. chkMonday) on the report, and set its 'Visible' property to 'False' so you don't see it when the report runs.
-- Use this code in the Control Source for the text box:

=IIf([chkMonday]=True,"Monday","")

-- Repeat this process for the other check boxes.


Incidentally, in Access 2000, this technique is useful when you export a report to Microsoft Word - I find that original check boxes just disappear from the Word document, so I have to display them as text messages in the report, to be able to see them in the Word document.

I hope this helps.




Bob Stubbs

 

[brent01]

Thanks guys your advice was very helpful. Both worked a treat. Much obliged!