If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
If-Then-Else statements
I'm presently studying Access and having difficulty understanding building
event codes. What I'm 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 -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
If-Then-Else statements
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 don't 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'll never work a day in your life." "BarbS via AccessMonster.com" u36617@uwe wrote in message news:a07ebc3e9801f@uwe... I'm presently studying Access and having difficulty understanding building event codes. What I'm 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 -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
If-Then-Else statements
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. "BarbS via AccessMonster.com" wrote: I'm presently studying Access and having difficulty understanding building event codes. What I'm 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 -- Message posted via http://www.accessmonster.com . |
#4
|
|||
|
|||
If-Then-Else statements
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 http://I.Am/DougSteele (no e-mails, please!) "Daniel Pineault" wrote in message ... 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. "BarbS via AccessMonster.com" wrote: I'm presently studying Access and having difficulty understanding building event codes. What I'm 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 -- Message posted via http://www.accessmonster.com . |
#5
|
|||
|
|||
If-Then-Else statements
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'm 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. "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "Daniel Pineault" wrote in message ... 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. "BarbS via AccessMonster.com" wrote: I'm presently studying Access and having difficulty understanding building event codes. What I'm 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 -- Message posted via http://www.accessmonster.com . . |
#6
|
|||
|
|||
If-Then-Else statements
Your understanding is correct, Daniel.
Note that while it's shorter code, I don't know that it's necessarily any more efficient. Not only that, but remember that others may be looking at your code months or years later, and it's important that they be able to figure it out too! For that reason, I usually put a comment in the code to identify what's being done. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Daniel Pineault" wrote in message ... 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'm 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. "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "Daniel Pineault" wrote in message ... 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. "BarbS via AccessMonster.com" wrote: I'm presently studying Access and having difficulty understanding building event codes. What I'm 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 -- Message posted via http://www.accessmonster.com . . |
#7
|
|||
|
|||
If-Then-Else statements
Thank you and the others for helping me try and figure this out.
Obviously I 'm just learning the code, but I've tried all suggestions to no avail. Maybe I'm 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'm 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: 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 I'm presently studying Access and having difficulty understanding building event codes. What I'm trying to do is suppress the printing of three [quoted text clipped - 11 lines] End If End Sub -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
If-Then-Else statements
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 and the others for helping me try and figure this out. Obviously I 'm just learning the code, but I've tried all suggestions to no avail. Maybe I'm 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'm 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: 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 I'm presently studying Access and having difficulty understanding building event codes. What I'm trying to do is suppress the printing of three [quoted text clipped - 11 lines] End If End Sub |
#9
|
|||
|
|||
If-Then-Else statements
Thank you John, for your reply. I'm using MS Access 2007. I believe the
PaymentID is NULL, but I tried your function just in case, that still didn't work. I also tried including all the labels, that didn't work either. I'm lost at what to do next. John Spencer wrote: 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 Thank you and the others for helping me try and figure this out. Obviously I 'm just learning the code, but I've tried all suggestions to no [quoted text clipped - 27 lines] End If End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 |
#10
|
|||
|
|||
If-Then-Else statements
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: Thank you John, for your reply. I'm using MS Access 2007. I believe the PaymentID is NULL, but I tried your function just in case, that still didn't work. I also tried including all the labels, that didn't work either. I'm lost at what to do next. You might need to hide the label also. [quoted text clipped - 19 lines] End If End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 |
|
Thread Tools | |
Display Modes | |
|
|