ACCESS2016 report .visible

[HMC62015]

Hi - thanks for the read!
I am looking for some advice on an access report that I need to hide fields and labels based on the underlying record.
The recordsource is a select query with Left Joins that may have a blank/null record (anyway).
I have marked .visible as either true or false on report open, then in detail_format, tried to write If Statements to turn the .visible on or off.


The labels I've turned off for null text box records all worked , but is the .visible for the text boxes false or are they hidden because it is null?
I have other lines of code (in bold) that do not hide two other labels and a checkbox....

Can anyone give a suggestion on what I would fix in this ?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)    
   If Not IsEmpty([RoHSCompliant]) Then 'Yes/No
    Me.Label111.Visible = True 'Label - always visible
    Me.RoHSCompliant.Visible = True 'Yes/no - always visible
    End If
    If IsNull([PartNote]) Then 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    Me.Label120.Visible = False 'Label - WORKS!
    Me.PartNote.Visible = False ' Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    End If
    If IsNull([OrderDeliveryNote]) Then 'Long Text
    Me.Label119.Visible = False 'label - WORKS!
    Me.OrderDeliveryNote.Visible = False 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    End If
    If Not IsNull([MfgSites.MSID]) Then 'Text Box - AutoNumber - recordsource(qry) can be null
    Me.Label112.Visible = True 'label - WON'T HIDE
    Me.MFGSite.Visible = True 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    Me.PAddress1.Visible = True 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    Me.PAddress2.Visible = True 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    Me.PCity.Visible = True 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    Me.PState.Visible = True 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    Me.PZIP.Visible = True 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    Me.Pcountry.Visible = True 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    End If
    If IsNull([CustNote]) Then 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    Me.Label91.Visible = False 'label - WORKS!
    Me.CustNote.Visible = False 'Text Box - recordsource(qry)can be null - is it hidden, or null on print preview?
    End If
    
    
End Sub


Private Sub Report_Open(Cancel As Integer)
    Me.Label112.Visible = False 'Label
    Me.Label111.Visible = False 'label
    Me.RoHSCompliant.Visible = False 'Yes/No
    Me.Label120.Visible = True 'label
    Me.PartNote.Visible = True 'Short Text
    Me.Label119.Visible = True 'Label
    Me.OrderDeliveryNote.Visible = True 'Long Text
    Me.MFGSite.Visible = False 'Short Text
    Me.PAddress1.Visible = False 'Short Text
    Me.PAddress2.Visible = False 'Short Text
    Me.PCity.Visible = False 'Short Text
    Me.PState.Visible = False 'Short Text
    Me.PZIP.Visible = False 'Short Text
    Me.Pcountry.Visible = False 'Short Text
    Me.Label91.Visible = True 'label
    Me.CustNote.Visible = True 'long Text


End Sub

[pbaldy]

I'm not sure I'm understanding, but you can test by setting the Border Style property of a textbox to Solid.

Presuming your report can return multiple records, you need an Else clause

If Whatever Then
  Me.ControlName.Visible = False
Else
  Me.ControlName.Visible = True
End If

Someone is sure to chime in with Boolean logic which would be a single line:

Me.ControlName.Visible = Not Whatever

but is confusing to some developers.

[HMC62015]

I'm not sure I'm understanding, but you can test by setting the Border Style property of a textbox to Solid.

Presuming your report can return multiple records, you need an Else clause

If Whatever Then
  Me.ControlName.Visible = False
Else
  Me.ControlName.Visible = True
End If

Someone is sure to chime in with Boolean logic which would be a single line:

Me.ControlName.Visible = Not Whatever

but is confusing to some developers.

Great news is - I was able to do the simple part and All of the labels are hiding for null text boxes.

For my checkbox, The VBA debugger is looking for 0, but my records aren't reading that way.
When I view the back end table the selections to filter the field are 'Yes' or 'No.'
Yes checkbox is the minority of these records: 1,196 records has 36 Yes.
When I view the record source (select query ), that is already joined to a certain group of records - and the result is empty - the selection is 'Is Not Selected'

I am not savvy on the boolean logic , I am sure I wouldn't be able to write it!

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)    
    If IsEmpty([RoHSCompliant]) Then
    Me.Label111.Visible = False
    Me.RoHSCompliant.Visible = False
    Else
    Me.Label111.Visible = True
    Me.RoHSCompliant.Visible = True
    End If

Would I change the table, and the resulting query, to show me -1 or 0, instead of the empty checkbox?
Trying both IsEmpty and Not IsEmpty had no change.
Is there syntax for 'Is not Selected' in VBA ?

[Gicu]

Could you please try this:

'simplified version
Me.Label111.Visible = Me.[RoHSCompliant]
Me.RoHSCompliant.Visible = Me.[RoHSCompliant]

@Paul: guilty as charged, sorry, didn't mean any disrespect, I just did not notice that in your first post. I agree with you and I usually don't use it like that, but just wanted to point out that with true boolean Yes\No field defined in the table you don't need the extra functions (IsNull(), IsEmpty(), etc.) as it will either be True or False (unlike the SQL server version which also allows for Nulls).

[pbaldy]

Try IsNull vs IsEmpty:

http://www.baldyweb.com/NullEmptyEtc.htm