Supressing Text in a Report

[Pam]

I have a report that lists contribution amounts for a certain month. I would
like everyone to receive the report. However, I want to supress the "thank
you for your contribution" line for those that didn't contribute that month.
This information is in the detail part of the report and here is the code I
am using (but is not working!):
*****
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Sum_Of_Amount Is Null Then

Label21.Visible = False
Label22.Visible = False
Label24.Visible = False
ContributionDate_By_Month.Visible = False
Sum_Of_Amount.Visible = False

End If

End Sub

****
I am still getting the report for each person. For the ones where no
contribution is made, the date and amount are just blanks.

Maybe I don't have the right code or maybe it should be somewhere other than
Detail_Format?

Thanks!

 

[Allen Browne]

For a code-free solution, right-click the labels and change them to text
boxes. Then set their Control Source to an expression that shows the caption
only if there is an amount, e.g.:
=IIf([Sum Of Amount] Is Null, Null, "Thanks!")

That will be more efficient than writing the code, but if you want to do it,
use:
If IsNull(Me.Sum_Of_Amount) Then
'Make em invisible.
Else
'Make em visible.
End If

You could optimise the code so that it only tests for null once and only
toggles the Visible property if necessary:
Dim bShow As Boolean
bShow = Not IsNull(Me.Sum_OfAmount)
If Me.Label21.Visible <> bShow Then
Me.Label21.Visible = bShow
Me.Label22.Visible = bShow
Me.Label24.Visible = bShow
End If
It still won't be as quick as the code-free solution.

Also, the code-free solution will work in Access 2007, whereas the section
events don't actually fire in the new Report view.

 

[Pam]

Thanks, Allen. I used the code-free solution and everything is working
perfectly!

For a code-free solution, right-click the labels and change them to text
boxes. Then set their Control Source to an expression that shows the caption
only if there is an amount, e.g.:
=IIf([Sum Of Amount] Is Null, Null, "Thanks!")

That will be more efficient than writing the code, but if you want to do it,
use:
If IsNull(Me.Sum_Of_Amount) Then
'Make em invisible.
Else
'Make em visible.
End If

You could optimise the code so that it only tests for null once and only
toggles the Visible property if necessary:
Dim bShow As Boolean
bShow = Not IsNull(Me.Sum_OfAmount)
If Me.Label21.Visible <> bShow Then
Me.Label21.Visible = bShow
Me.Label22.Visible = bShow
Me.Label24.Visible = bShow
End If
It still won't be as quick as the code-free solution.

Also, the code-free solution will work in Access 2007, whereas the section
events don't actually fire in the new Report view.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that lists contribution amounts for a certain month. I
would
like everyone to receive the report. However, I want to supress the
"thank
you for your contribution" line for those that didn't contribute that
month.
This information is in the detail part of the report and here is the code
I
am using (but is not working!):
*****
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Sum_Of_Amount Is Null Then

Label21.Visible = False
Label22.Visible = False
Label24.Visible = False
ContributionDate_By_Month.Visible = False
Sum_Of_Amount.Visible = False

End If

End Sub

****
I am still getting the report for each person. For the ones where no
contribution is made, the date and amount are just blanks.

Maybe I don't have the right code or maybe it should be somewhere other
than
Detail_Format?