Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    MS Access 2010: Removing Blank Lines from a Report

    I have an Access 2010 Report that looks like this (obviously a fictitious dataset):

    20120228 Access Reoprt Sample.JPG


    The data for this Report is contained in a Query as follows:

    20120228 Access Query Sample.JPG

    Looking at the Report again, we can see that for ID 124 there are 2 rows (B & C) that contain no data from the Query, similarly for ID 125, one row has no data. Displaying these rows is a waste of report screen and paper space. The question is How can I space the white space?

    Where, and what VBA code can I include in my Report so that the empty fields do no display in the Report.

    I am thinking along the lines of:

    Private Sub Detail_Format(Cancel As Integer, FormatCount as Integer)

    If isnull(Subaction_C) = True then
    Detail.Height = 5
    Else
    If isnull(Subaction_B) = True then
    Detail.Height = 3.5
    Else
    If isnull(Subaction_A) = True then
    Detail.Height = 2
    Else
    Detail.Height = 0
    End If
    End If
    End If

    Unfortunately my knowledge of Access VBA programming is very limited and I havent worked out how to make this work.

    I hope there are some bright sparks out there who can point me in the right direction.

    Thanks in anticipation for any assistance provided.

    Trevor
    Last edited by BygAuldByrd; 2012-02-27 at 23:36.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I tried to get this to work using code to set the subaction labels to be invisible when not needed, but it did not work.. could not see why not.

    This approach did work. Delete the labels, and instead incorporate the label into the Text box.
    • Rename each of the text boxes to txtSubactionA, txtSubactionb and txtSubactionC.
    • Set the Control source of the text boxes to: ="A "+[SubactionA] , ="B "+[SubactionB], ="C "+[SubactionC]
    • by using + instead of & the initial letter is not displayed in the field value is null.
    • Set the CanShrink property of each control, and to the Detail Section to Yes.

    sunactions.gif

    Technically the subactions should be in a separate table (there is more than one subaction for each ID) If you did it that way you would not have this problem in the first place.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    Hi John,

    Sorry for the delay in responding but only just got back to trying to sort out this issue in my Report - an extend caravan trip got in the way!

    Your suggested solution of using a Textbox works a treat. Many thanks.

    I also wanted it to work when the data was a date and found that the following formula in the Textbox Control Source worked:
    ="A " + Format([Subactiondate],"yyyymmdd")

    This displayed as "A 20120416" when the the date was 16 Apr 2012.

    Without the Format function I got a #Type! error because I was tyring to add a string to a date.

    In a variation, I wanted to test if the date was present but I didn't want to display it, so I tried
    ="A" + Format([Subactiondate]," ")

    This displayed as "A ", which suited my purpose perfectly. (Note the space between the quotes in the Format function.)

    Again, belated thanks for your kind assistance.

    Trevor

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •