If..then statement involving checkboxes

In Access 2003: A table with four yes/no fields is part of a query used as data source for a report. The yes/no fields show in the datasheet and the report as checkboxes; the default value of the field is "no". The report pulls in a subreport. I want the subreport to be visible ONLY if any of the yes/no fields contain a yes (showing as a check in the checkbox). I tried an if then statement like this:

If Field1 ="no" AND Field2 = "no" AND Field 3 = "no" AND Field 4 = "no" then
[Subreport].visible = false
Else [Subreport].visible=true
Endif

I also tried a similar statement using IsNull [Field1] AND etc.

I haven't used Access VBA since 2002 using Access 2000 - I'm lost. How should the "if" part be stated?

re: If..then statement involving checkboxes

Your statement is only going to work if ALL the fields are set to No/False.
What you need to do is replace your AND's with OR's, and set the condition to True.
Then if any of the fields are 'Yes', it will show that record. Also, boolean values do not get wrapped with quotes.. those are only for String values.

re: If..then statement involving checkboxes

Here's the code as entered:

Private Sub Report_Open(Cancel As Integer)
If COA_Required = True Or Officer_Required = True Or Designated_Eng_in_Resp_Charge_Required = True Or Local_Office_Designated_Engineer_Required = True Then
[Qualifiers].Visible = True
Else
[Qualifiers].Visible = False
End If

End Sub

The subreport is not visible. If I flip the [Qualifiers].Visible to

Private Sub Report_Open(Cancel As Integer)
If COA_Required = True Or Officer_Required = True Or Designated_Eng_in_Resp_Charge_Required = True Or Local_Office_Designated_Engineer_Required = True Then
[Qualifiers].Visible = False
Else
[Qualifiers].Visible = True
End If

End Sub

the subreport is visible in all instances, even if all four fields are empty.

re: If..then statement involving checkboxes

You actually had it almost correct the first time.
Your problem was you used "no" instread of false
or

In the above 2 examples we are saying if ALL of the checkboxes are false then hide subreport
otherwise show it


alternatively

or

The final 2 examples says if ANY of the checkboxes are ticked then show subreport
otherwise hide it

AND's and OR's can be tricky to get your head around sometimes.

I find ANY when using OR and ALL when using AND are useful aids

re: If..then statement involving checkboxes

Delerna, thank you for your response. I tried both your suggestions for if ALL of the checkboxes are false then hide subreport (which is what I'm looking for). Both resulted in the subreport being hidden in every instance.

Any further suggestions are absolutely welcome.

re: If..then statement involving checkboxes

Can you Upload the Database as an Attachment for us to see first hand?

re: If..then statement involving checkboxes

Where are you running this code from. Is it running on the afterupdate of the checkboxes. If it is are you running
after the update, and before the evaluation, so that it is reading the current entered data

re: If..then statement involving checkboxes

Unfortunately, I can't, because of the confidentiality of the data.

re: If..then statement involving checkboxes

Put some breakpoints on the code in question.
I am wondering if it is even getting executed?

re: If..then statement involving checkboxes

• I guess you need to place the code in other event handler - Detail_OnPrint or Detail_OnFormat.
• Code size could be reduce to one line.
  [SubreportName].Visible=Field1 Or Field2 Or Field 3 ...

re: If..then statement involving checkboxes

Does this only work with true/false field values (i am assuming yes)? How does the get interpreted for one value? Does True always beat False?

re: If..then statement involving checkboxes

When passing boolean expressions, testing for True is the default. Even when you use AND/OR with a combination of boolean values, you will still end up with one final value. This value then is applied to the .visible property.

re: If..then statement involving checkboxes

Nice to know, thanks!

re: If..then statement involving checkboxes

It is an expression (like arithmetic one) which is being evaluated.
Boolean algebra (introduction)