none
Access 2007 reports RRS feed

  • Question

  • I have a report that produces 0 values. I want to hide the whole line from the report. Any suggestions?
    Wednesday, June 8, 2016 4:06 PM

Answers

  • If you are trying to suppress the entire record on the report, then the filter suggested by the DB Guy will do the trick.

    If you want to hide a textbox that contains "0" and eliminate the space associated with the line, while leaving other fields in the record displayed as-is, then try this:

    1.  Set the control source of your textbox to this, including the = sign:

        
    = iif([MyFieldName] = 0, Null, MyFieldName)

    2. If there is an associated label, change it to a textbox (call it txtCaption for example), and set the control source to this:

    = iif([MyFieldName] = 0, Null, "The caption text")

    3.  Set the height of both the caption textbox and the  original textbox to zero.

    4. Set the Can Grow property of both textboxes to YES.

    That setup will suppress the textbox, label and white space associated with the textbox if the field contains a zero, while continuing to display other fields in your record.

    Note that can grow/can shrink only work if your textbox is the only control at that vertical position on the report, so this suggestion assumes that there are no other controls lined up horizontally with your textbox. 


    Miriam Bizup Access MVP

    • Proposed as answer by David_JunFeng Friday, July 22, 2016 8:00 AM
    • Marked as answer by David_JunFeng Friday, July 22, 2016 8:00 AM
    Thursday, June 16, 2016 10:38 AM

All replies

  • Can you try filtering the report to exclude the 0 values? Just a thought...
    Wednesday, June 8, 2016 4:29 PM
  • Hi pennyhart,

    please use the code below to hide the field which contains 0 on report

    Private Sub Report_Open(Cancel As Integer)
    Dim VisEnable As String
     If Me.result < 1 Then
     VisEnable = False
     Else
     VisEnable = True
     End If
     Me.result.Visible = VisEnable
    
    End Sub
    

    place the code inside Report_Open event. change the result with your field name.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 9, 2016 6:24 AM
    Moderator
  • Thanks for your help.

    Thursday, June 9, 2016 1:04 PM
  • Hi pennyhart,

    if you think any of the above given suggestion helped you to solve your issue then I would recommend you to mark the suggestion as an Answer.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 10, 2016 6:26 AM
    Moderator
  • Hello Deepak,

    This did not solve my problem.

    Wednesday, June 15, 2016 3:36 PM
  • Hello Deepak,

    This did not solve my problem.


    And filtering the report was not an option?
    Wednesday, June 15, 2016 3:39 PM
  • Hi pennyhart,

    you mentioned that your issue is not solved.

    can you please try to describe your issue with some more details.

    you only describe 1 line in original post.

    base on that one line we try to provide you a solution that did not worked for you.

    so it is helpful to us if you give some detail with some example or steps from that we can reproduce your issue on our side and try to provide you some useful suggestion.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 16, 2016 12:39 AM
    Moderator
  • If you are trying to suppress the entire record on the report, then the filter suggested by the DB Guy will do the trick.

    If you want to hide a textbox that contains "0" and eliminate the space associated with the line, while leaving other fields in the record displayed as-is, then try this:

    1.  Set the control source of your textbox to this, including the = sign:

        
    = iif([MyFieldName] = 0, Null, MyFieldName)

    2. If there is an associated label, change it to a textbox (call it txtCaption for example), and set the control source to this:

    = iif([MyFieldName] = 0, Null, "The caption text")

    3.  Set the height of both the caption textbox and the  original textbox to zero.

    4. Set the Can Grow property of both textboxes to YES.

    That setup will suppress the textbox, label and white space associated with the textbox if the field contains a zero, while continuing to display other fields in your record.

    Note that can grow/can shrink only work if your textbox is the only control at that vertical position on the report, so this suggestion assumes that there are no other controls lined up horizontally with your textbox. 


    Miriam Bizup Access MVP

    • Proposed as answer by David_JunFeng Friday, July 22, 2016 8:00 AM
    • Marked as answer by David_JunFeng Friday, July 22, 2016 8:00 AM
    Thursday, June 16, 2016 10:38 AM
  • I've tried filtering but I'm not sure that I did it right.
    Thursday, June 30, 2016 12:07 PM
  • Deepak,

    The filtering I tried from Miriam Bizup Access MVP only filtered the one text box. I need it to filter (not show) the whole line on the report.

    Sample of the report.

    Date    PO Number    Customer    Item    PO Qty     Published Cost   PO Cost    Difference

    01/01/15   PO35456    Penny      Pallets     100           $5.05               $5.05          0

    01/01/15   PO 35457    Penny      Pallets     100          $6.00               $5.00         $1.00

     Published Cost is taken from several text boxes hidden in the report with the IIf statement.

    PO cost is taken from the table associated with report.

    The report is grouped by the Sales person. I would like the whole line of "0" to disappear from the report, so it does not print all of that sales person's 0 difference.

    I hope this helps.

    Penny

    Thursday, June 30, 2016 3:38 PM
  • Hi pennyhart,

    yes it is work for only 1 textbox because you had set the control source of only 1 textbox.

    you had mentioned that ,"I would like the whole line of "0" to disappear from the report,"

    did you mean whole record instead of only that field in the record?

    if so then you can also try like below.

    Private Sub Report_Open(Cancel As Integer)
    
     If Me.result =0 Then
     Cancel = true
     End If
     End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, July 1, 2016 8:01 AM
    Moderator