Suppress labels on report for fields with no data (access 2000)

Topic

New Reply

We have a report that is very basic. In three columns, it just repeats the fields in the source table. So each record takes up at least 7 lines to print, since there are 7 fields. The user is wanting to keep from printing fields that have no data in them. I know that I can set the property for the text box on the report to “Can Shrink” and it will skip over the field itself, but the label prints anyway. Is there an easy way to leave off the label if the text box shrinks?

Reply | Quote

Replies

You can write code for the OnFormat event of the detail section of the report. It could look like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
[lblField1].Visible = Not IsNull([txtField1])
[lblField2].Visible = Not IsNull([txtField2])
End Sub

Replace txtField1 by the name of a textbox, and lblField1 by the name of the attached label, etc. You must have a line for each of the text boxes that may contain null values. Don’t forget to set the CanShrink property of the detail section to Yes too.

Reply | Quote

I want to do this same thing on my report, but I have three fields and I want them invisible only if all three are blank…what would the code look like in this case? Would it be an If..statement? Thanks, I’m just learning…

Sarah

Reply | Quote

You said:
<>

I presume that you meant the labels to be invisible, in which case you could do the following in the OnFormat event of the Detail section of your report.

If IsNull(Field1) and IsNull(Field2) and IsNull(Field3) then
Field1Label.Visible = False
Field2Label.Visible = False
Field3Label.Visible = False
Else
Field1Label.Visible = True
Field2Label.Visible = True
Field3Label.Visible = True
Endif

HTH
Pat

Reply | Quote

In my previous post I mantioned to set the Visible property of the Label to True or False.
In fact you can set the Control’s visible property (not the Label’s property) to True or False, this will also set the Label’s Visible property to True or False accordingly.
Sorry for any confusion.
Pat

Reply | Quote

Thanks for your help Pat. I’ll try it out!

Reply | Quote

BTW, which island are you from?
Pat

Reply | Quote

Lived in Key West for a few years…not really sure why I left…

Reply | Quote