Suppressing printing when values are zero

[Guest]

I have a report where some of the values are blank. Is there a way to not
print the values and their associated titles is the fields are blank?

Thanks,
Joan

 

[Guest]

Joan:

The subject of your post says 'zero', the body text says 'blank'??
Whichever is the case you put code in the Format event procedure of whichever
section the controls are in. If it's zero:

Me.YourLabel.Visible = Me.YourControl <> 0
Me.YourControl.Visible = Me.YourControl <> 0

If its Null:

Me.YourLabel.Visible = Not IsNull(Me.YourControl)
Me.YourControl.Visible = Not IsNull(Me.YourControl)

Strictly speaking the second line isn't necessary in the above case as Null
controls can shrink without being hidden.

If it could be Null or zero:

Me.YourLabel.Visible = Nz(Me.YourControl,0) <> 0
Me.YourControl.Visible = Nz(Me.YourControl,0) <> 0

If it could be Null or a zero-length string:

Me.YourLabel.Visible = Len(Me.YourControl & "") > 0
Me.YourControl.Visible = Len(Me.YourControl & "") > 0

You can avoid leaving a gap vertically where the control would have been by
setting its CanShrink property to True, provided that there are no
non-shrinking controls alongside it, including graphical elements such as
lines or boxes. Be sure to set the section's CanShrink property to True also
if you don't want empty space at the bottom of each detail record if any of
the controls have shrunk.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Ken for the information, but I am still lost. The detailed section is
full of fields. I want to individually only print the fields that are zero.
I can't set the entire section to not print when some fields are zero and
some aren't. I am sorry for the confusion before. Thanks so much for your
help.
Joan

 

[Guest]

Joan:

Apologies for the delay in replying; I've been on the road. The solutions I
sent you will selectively hide individual controls and their associated
labels, not the entire section. You need two lines of code for each control
in question, one for the bound control and one for its label. Which of the
alternatives I gave you is the right one in your case depends on whether the
controls are to be hidden when Null (empty of any value), zero (containing a
value of 0) or containing a zero length string. If it doubt use the code for
testing for zero or Null, or a zero length string or Null, depending on the
data type in question.

Ken Sheridan
Stafford, England

 

[Guest]

Ken,
You are a genius! That worked wonderful. Thanks so much. You saved me so
much grief. Thank you.
Joan

 

[Guest]

Ken,
The first line of code I put in worked wonderful. But the second will not.
I am not very good with code. My label is Setup_Fee_Label and the field is
Setup Fee
But I keep getting an unexpected end to the statement.



Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.Setup_Fee_Label.Visible = Me.Setup Fee <> 0

End Sub

My next variable is:
Hour (Rate 1)_Label and the control is Hour (Rate 1)

Thanks again.

 

[John W. Vinson]

Ken,
The first line of code I put in worked wonderful. But the second will not.
I am not very good with code. My label is Setup_Fee_Label and the field is
Setup Fee
But I keep getting an unexpected end to the statement.



Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.Setup_Fee_Label.Visible = Me.Setup Fee <> 0

End Sub

My next variable is:
Hour (Rate 1)_Label and the control is Hour (Rate 1)

Thanks again.

If you use blanks or special characters in control names, you MUST enclose the
names in square brackets. Blanks are meaningful! Me.Setup Fee in VBA is two
values - Me.Setup, and a meaningless word Fee.

Try

Me.Setup_Fee_Label.Visible = Me.[Setup Fee] <> 0

Similarly, when your code refers to any other control with such names, enclose
the name in brackets.

I've had some wierd things happen with field and control names containing
parentheses, even *with* brackets - I'd suggest avoiding such names. The user
will never need to see fieldnames or control names, so you may be better off
with a naming convention that uses only letters, numbers and underscores in
control names - e.g. a fieldname Hour_Rate1 (note that Hour is a reserved
word!!) would be bound to a textbox named txtHour_Rate1 with a label named
lblHour_Rate1.

John W. Vinson [MVP]

 

[Guest]

Thank you so much. I will try this. Thanks for all your help.
Joan

Ken,
The first line of code I put in worked wonderful. But the second will not.
I am not very good with code. My label is Setup_Fee_Label and the field is
Setup Fee
But I keep getting an unexpected end to the statement.



Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.Setup_Fee_Label.Visible = Me.Setup Fee <> 0

End Sub

My next variable is:
Hour (Rate 1)_Label and the control is Hour (Rate 1)

Thanks again.

If you use blanks or special characters in control names, you MUST enclose the
names in square brackets. Blanks are meaningful! Me.Setup Fee in VBA is two
values - Me.Setup, and a meaningless word Fee.

Try

Me.Setup_Fee_Label.Visible = Me.[Setup Fee] <> 0

Similarly, when your code refers to any other control with such names, enclose
the name in brackets.

I've had some wierd things happen with field and control names containing
parentheses, even *with* brackets - I'd suggest avoiding such names. The user
will never need to see fieldnames or control names, so you may be better off
with a naming convention that uses only letters, numbers and underscores in
control names - e.g. a fieldname Hour_Rate1 (note that Hour is a reserved
word!!) would be bound to a textbox named txtHour_Rate1 with a label named
lblHour_Rate1.

John W. Vinson [MVP]

 

[Eric]

Dear Ken..

I would like to get an advise from you, i'm not familar with the "coding"

I 've create a form for my invoice

It has 2 fields -Product and price

Product 1 - price 1
product 2 - price 2
product 3 - price 3


a total field, which is =([price1]+[price 2] +[price3])
however, it does not work if any of the field is 'null".

When i print, i want to print only the field is not null. For example, if
product 2 and product 3 are null, i do not have to print

How can i do it

Appreciate if you would help me

Eric