If..then statement involving checkboxes

[curious80237]

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?

[Megalog]

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.

If Field1 = True OR Field2 = True OR Field 3 = True OR Field 4 = True Then
     [Subreport].Visible = False
Else 
     [Subreport].Visible = True
Endif

[curious80237]

Here's the code as entered:

Private Sub Report_Open(Can cel As Integer)
If COA_Required = True Or Officer_Require d = True Or Designated_Eng_ in_Resp_Charge_ Required = True Or Local_Office_De signated_Engine er_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(Can cel As Integer)
If COA_Required = True Or Officer_Require d = True Or Designated_Eng_ in_Resp_Charge_ Required = True Or Local_Office_De signated_Engine er_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.

[Delerna]

You actually had it almost correct the first time.
Your problem was you used "no" instread of false
[code=vba]
If Field1 = False AND Field2 = false AND Field3 = false AND Field4 = false Then
[Subreport].Visible = False
Else
[Subreport].Visible = True
Endif
[/code]

or

[code=vba]
If not Field1 AND not Field2 AND not Field3 AND not Field4 Then
[Subreport].Visible = False
Else
[Subreport].Visible = True
Endif
[/code]
[code=vba]
If not Field1 AND not Field2 AND not Field3 AND not Field4 Then
[Subreport].Visible = False
Else
[Subreport].Visible = True
Endif
[/code]
In the above 2 examples we are saying if ALL of the checkboxes are false then hide subreport
otherwise show it


alternatively

[code=vba]
If Field1=true OR Field2=true OR Field3=true OR Field4=true Then
[Subreport].Visible = True
Else
[Subreport].Visible = False
Endif
[/code]

or

[code=vba]
If Field1 OR Field2 OR Field3 OR Field4 Then
[Subreport].Visible = True
Else
[Subreport].Visible = False
Endif
[/code]

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

[curious80237]

tried again

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.

[ADezii]

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.

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

[kstevens]

Where are you running this code from. Is it running on the afterupdate of the checkboxes. If it is are you running

if me.dirty then me.dirty = false

after the update, and before the evaluation, so that it is reading the current entered data

[curious80237]

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

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

[Delerna]

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

[FishVal]

• 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 ...

[kstevens]

• Code size could be reduce to one line.
  [SubreportName].Visible=Field1 Or Field2 Or Field 3 ...

Does this only work with true/false field values (i am assuming yes)? How does the

.Visble = True or False or False or False

get interpreted for one value? Does True always beat False?

[Megalog]

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.

[kstevens]

Nice to know, thanks!

[FishVal]

Does this only work with true/false field values (i am assuming yes)? How does the

.Visble = True or False or False or False

get interpreted for one value? Does True always beat False?

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