Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
Control Value Is Null
Me.PaymentID_Label.Visible = Not IsNull([PaymentID])
Me.PaymentAmt_Label.Visible = Not IsNull([PaymentID])
Me.PaymentDate_Label.Visible = Not IsNull([PaymentID])
End Sub
This is the solution for the rptCitationsAndPaymentsModified report to
hide the LABELS when there is no data in them for the associated Text Box
in the Tophill.accdb project.
We test PaymentID Text Box for a Null Value, meaning we have NO DATA
because the joker hasn't paid his or her ticket. When that check comes
back true, then we hide the LABELS not the text boxes themselves(because
they're already empty!). Thus when we do the print preview on the report,
it omits the three LABELS for Payment ID, Payment Amt, and Payment Date
because PaymentID text box has no data to display.
In theory you could take this even further, hiding ALL labels so you end
up with blank lines in the report between people who have actually paid
their ticket but then you are hiding text boxes (with data) AND labels.
I'm not claiming to be new or revolutionary, I just riffed on the code and
took a look at what we were hiding. It wasn't the TEXT BOX it was the
LABEL. So changing the VB code to reflect got me the desired resolution.
We didn't need to hide the text box because it was already empty, just
it's label.
Al Campagna wrote:
Barb,I agree with Ken Sheridan.
14-Dec-09
Barb,
I agree with Ken Sheridan. The code we suggested is OK, so the
PaymentID may be something other than Null.
Is it Null ?
Is it "" ?
Is it 0 (zero)?
A trick I use in these situations is to set up the Format for the
PaymentID control...
If either a Positive or negative value = display normally (123.45
or -123.45)
If zero = display as .00
If Null = Display as "Null"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you will never work a day in your life."
Previous Posts In This Thread:
If-Then-Else statements
I am presently studying Access and having difficulty understanding
building
event codes. What I am trying to do is suppress the printing of three
controls in a report if they are Null. This is how I wrote the code, but
it
is not working. Can someone please help. Thank you,
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
control value is null'
If IsNull([PaymentID]) Then
[PaymentID] , [PaymentAmt], [PaymentDate].Visible = False
Else: [PaymentID].Visible = True
End If
End Sub
--
Barb,Check Help for syntax assistance on the For-Else-Then statement.
Barb,
Check Help for syntax assistance on the For-Else-Then statement.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull([PaymentID]) Then
[PaymentID].Visible = False
[PaymentAmt].Visible = False
[PaymentDate].Visible = False
Else
[PaymentID].Visible = True
[PaymentAmt].Visible = True
[PaymentDate].Visible = True
End If
End Sub
If you do not make Amt and Date visible again, in the Else statement,
they will never be visible again during that report printout.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you will never work a day in your life."
How about
rivate Sub Detail_Format(Cancel As Integer, FormatCount As
How about:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
control value is null'
If IsNull([PaymentID]) = True Then
Me.PaymentID.Visible = False
Me.PaymentAmt.Visible = False
Me.PaymentDate.Visible = False
Else
Me.PaymentID.Visible = True
Me.PaymentAmt.Visible = True
Me.PaymentDate.Visible = True
End If
End Sub
--
Hope this helps,
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples:
http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
:
Or shorter
rivate Sub Detail_Format(Cancel As Integer, FormatCount As
Or shorter:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Suppress the printing of the Payment Information when the PaymentID
control value is null'
Me.PaymentID.Visible = Not IsNull([PaymentID])
Me.PaymentAmt.Visible = Not IsNull([PaymentID])
Me.PaymentDate.Visible = Not IsNull([PaymentID])
End Sub
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Douglas,If I understand properly (please correct me)it will evaluate the
Is
Douglas,
If I understand properly (please correct me)
it will evaluate the Is Null() and then inverse the boolean to apply the
visible property.
so if PaymentID is null then is will return True and therefor the visible
property will be set to Not True (therefore False).
Always learning. Thank you for the clean and easy code! I am sure I will
be
able to use this principle in many other places.
--
Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples:
http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
:
Your understanding is correct, Daniel.
Your understanding is correct, Daniel.
Note that while it is shorter code, I do not know that it is necessarily
any
more efficient. Not only that, but remember that others may be looking at
your code months or years later, and it is important that they be able to
figure it out too! For that reason, I usually put a comment in the code to
identify what is being done.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Thank you and the others for helping me try and figure this out.
Thank you and the others for helping me try and figure this out.
Obviously I 'm just learning the code, but I have tried all suggestions to
no
avail. Maybe I am not clearly saying what I need to do. I need to
suppress
the printing of three controls in a report when the PaymentID control
value
is null. The three controls are as you have listed below, PaymentID,
PaymentAmt, and PaymentDate. I am entering this code in the Report Detail
section, Event, On Format and the three commands and their labels are
still
showing on the report (null). Do you have any ideas of what I might be
doing
wrong? Again, thank you for your help,
Daniel Pineault wrote:
--
You might need to hide the label also.What version of Access are you
using?
You might need to hide the label also.
What version of Access are you using?
Are you sure the value of PaymentID is NULL?
Is it possible that it is a zero-length string?
You could use something like the following to handle Nulls, zero-length
strings, and strings that consist of multiple spaces.
Me.PaymentID.Visible = Len(Trim(Me.PaymentID & ""))>0
Me.PaymentAmount.Visible = Len(Trim(Me.PaymentID & ""))>0
Me.PaymentDate.Visible = Len(Trim(Me.PaymentID & ""))>0
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
BarbS via AccessMonster.com wrote:
Thank you John, for your reply. I am using MS Access 2007.
Thank you John, for your reply. I am using MS Access 2007. I believe the
PaymentID is NULL, but I tried your function just in case, that still did
not
work. I also tried including all the labels, that did not work either. I
am
lost at what to do next.
John Spencer wrote:
--
One possibility is that the value of the PaymentID column is zero rather
One possibility is that the value of the PaymentID column is zero rather
than
being Null. I think by default Access gives columns of number data type a
DefaultValue property of zero, so this could be the case if the column to
which the control in the report is bound is a foreign key in a table
referencing the primary key of a Payments table or similar. If the
display
control is looking up the value from the referenced table then it will
appear
empty as there is no row in the referenced table with key value of zero.
If this could explain the behaviour try testing for zero rather than Null:
Me.PaymentID.Visible = (Me.PaymentID <> 0)
Me.PaymentAmount.Visible = (Me.PaymentID <> 0)
etc.
Alternatively, if the above scenario is the case, bind the control to the
primary key of the referenced table (Payments), not the foreign key of the
referencing table. The query would need to use an outer join of course
and
return the referenced primary key, not the foreign key which references
it.
The control would then be Null if there is no matching row in the
referenced
table, so testing for Null should work.
Ken Sheridan
Stafford, England
BarbS wrote:
--
Barb,I agree with Ken Sheridan.
Barb,
I agree with Ken Sheridan. The code we suggested is OK, so the
PaymentID may be something other than Null.
Is it Null ?
Is it "" ?
Is it 0 (zero)?
A trick I use in these situations is to set up the Format for the
PaymentID control...
If either a Positive or negative value = display normally (123.45
or -123.45)
If zero = display as .00
If Null = Display as "Null"
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you will never work a day in your life."
Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Reflection Effect
http://www.eggheadcafe.com/tutorial...-beab-49bd76e20b9b/wpf-reflection-effect.aspx
