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 |
#11
|
|||
|
|||
If-Then-Else statements
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... #.00 ; -#.00 ; .00 ; \Null 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'll never work a day in your life." "BarbS via AccessMonster.com" u36617@uwe wrote in message news:a08e57f01b230@uwe... 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 |
#12
|
|||
|
|||
If-Then-Else statements (hiding labels for empty fields)
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: On Saturday, December 12, 2009 4:24 PM BarbS via AccessMonster.com wrote: 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 -- Message posted via http://www.accessmonster.com On Saturday, December 12, 2009 5:04 PM Al Campagna wrote: 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." On Saturday, December 12, 2009 5:45 PM Daniel Pineault wrote: How about:Private 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. "BarbS via AccessMonster.com" wrote: On Sunday, December 13, 2009 7:46 AM Douglas J. Steele wrote: Or shorter:Private 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 http://I.Am/DougSteele (no e-mails, please!) On Sunday, December 13, 2009 10:41 AM Daniel Pineault wrote: 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. "Douglas J. Steele" wrote: On Sunday, December 13, 2009 11:07 AM Douglas J. Steele wrote: 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 http://I.Am/DougSteele (no private e-mails, please) On Sunday, December 13, 2009 2:48 PM BarbS via AccessMonster.com wrote: 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: -- Message posted via http://www.accessmonster.com On Sunday, December 13, 2009 7:20 PM John Spencer 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: On Sunday, December 13, 2009 10:12 PM 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: -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 On Monday, December 14, 2009 7:05 AM KenSheridan via AccessMonster.com 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: -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 On Monday, December 14, 2009 10:24 AM Al Campagna 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/tutorials...on-effect.aspx |
#13
|
|||
|
|||
If-Then-Else statements (hiding labels for empty fields)
Jake
Are you trying to do this in a report? Be aware that there's a Can Grow and a Can Shrink property associated with controls. If there's no value (i.e., Null) in a control, that control can be made to shrink/disappear, using this property. However, if you have labels that are NOT attached to their respective controls (e.g., a label control not attached to "its" textbox control), the labels won't shrink. The simple solution is to attach your labels to their controls, so that the Can Shrink property applies to both... Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. Jake Davis wrote in message ... 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: On Saturday, December 12, 2009 4:24 PM BarbS via AccessMonster.com wrote: 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 -- Message posted via http://www.accessmonster.com On Saturday, December 12, 2009 5:04 PM Al Campagna wrote: 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." On Saturday, December 12, 2009 5:45 PM Daniel Pineault wrote: How about:Private 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. "BarbS via AccessMonster.com" wrote: On Sunday, December 13, 2009 7:46 AM Douglas J. Steele wrote: Or shorter:Private 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 http://I.Am/DougSteele (no e-mails, please!) On Sunday, December 13, 2009 10:41 AM Daniel Pineault wrote: 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. "Douglas J. Steele" wrote: On Sunday, December 13, 2009 11:07 AM Douglas J. Steele wrote: 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 http://I.Am/DougSteele (no private e-mails, please) On Sunday, December 13, 2009 2:48 PM BarbS via AccessMonster.com wrote: 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: -- Message posted via http://www.accessmonster.com On Sunday, December 13, 2009 7:20 PM John Spencer 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: On Sunday, December 13, 2009 10:12 PM 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: -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 On Monday, December 14, 2009 7:05 AM KenSheridan via AccessMonster.com 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: -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 On Monday, December 14, 2009 10:24 AM Al Campagna 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/tutorials...on-effect.aspx |
|
Thread Tools | |
Display Modes | |
|
|