Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    skipping blank fields in a report

    Is there a way to skip a field in a report if there is a null value in the empty field , when printing. For example, we have a report with a typical name-address-phone number. When printing the records out there are some names without address and phone numbers, and when it prints there name appears and two blank lines. We want the infomation to be moved up so that the two blanks don't show.

    Any help would be appreciated.

    -Kelley

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: skipping blank fields in a report

    I'm just learning but, if you have a query that provides the data for your report, why not change your query by putting criteria in the fields that are coming up blank. The criteria would specify that it only chooses fields that are not blank. Then you could run your report. If later you wanted to see those particular fields you could change your query back by ommitting the criteria.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping blank fields in a report

    Don't know that would work here because not all the same fields are blank. One one record, a person may only be missing a phone number, while on another record, that person may be missing an address. I don't want to have separate queries looking for all the blanks on each field.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: skipping blank fields in a report

    This is easy assuming these controls are stacked like this:

    control1
    control2
    control3

    You can squeeze the vertical height of each control down to nearly zero and move them up close together vertically. Then set the CanGrow and CanShrink properties of both the controls and the section to true. What will happen is that if there is a value in the field, the control will expand to normal height and push any others down. If there is nothing in the field, it won't expand and you will not see a blank line.

    This will only work with controls that hold values (i.e., textbox controls), not with labels, so watch out for that. Also be aware the if there are controls to the right of these, their alignment will be changed by the growth of these controls.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping blank fields in a report

    Charlotte,

    I tried that, and I'm still left with big gaping holes in the report. I'm very baffled by this. I would rather not post the entire database on here, is there any way that I can email you the database to look at individually? I'm hoping that I'm missing something really simple.

    -Kelley

  6. #6
    New Lounger
    Join Date
    Feb 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping blank fields in a report

    Hi
    There are two reasons why controls do not shrink when you tell them to:
    1 The controls are overlapping - check all the "Top" and "Height" properties and make sure there is a small gap between (It only needs to be 0.5mm);
    2 There are other controls on the same lines as these. Since the Can Shrink property operates across the entire width of the report, these other controls will prevent your address fields from shrinking. The solution here is to put your address fields into a sub-report with Can Shrink set to yes on all the controls and the section. They can then shrink within their own report and will not be affected by other controls in the main report.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping blank fields in a report

    The controls are not overlapping. Even when I moved them away from each other, it still does not work.

    I checked the top and height settings. Not sure what the height setting has to do with anything though, doesn't that control the location of the control from the top of the section? The height of all the controls is 0.125. There are no other controls to the right of the address controls, as they are in the columnar format, one record displayed vertically, not the tabular going left to right. Since there are not any controls to the right, I'm not sure if I need to try the subreport feature. If I have time, I will try.

    Thanks,

    Kelley

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: skipping blank fields in a report

    The height property controls the display height of the control. It's the top property that determines where in a section the control appears. You need to make your control heights around 0.04" each, so that they're nothing more than thin lines in the design. Move them up close under one another but don't allow them to overlap. With the CanGrow property turned on, the controls that have data will expand and push those below them down, while those without data will stay shrunken.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping blank fields in a report

    Have you tried the MoveLayout and associated commands. Put in the On Format event they will eliminate the blank lines for null fields:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If IsNull(Me!Address) Then
    Me.MoveLayout = False
    Me.NextRecord = True
    Me.PrintSection = False
    End If
    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: skipping blank fields in a report

    MoveLayout applies to the whole section, not just to a particular control.
    Charlotte

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping blank fields in a report

    Brian, thanks for the info. I'll be sure and check it out.

    -Kelley

  12. #12
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping blank fields in a report

    Hi Kelley: If you're using Access '97 you'll find a very effective solution in RptSmp97.mdb sample database from MS. It's described in KB Article <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q95/3/90.asp?PR=ACC>Q95390</A>. You'll note the article points out an interesting fact, if a field has a hidden value, perhaps a space, it can't shrink. I can't see why this sort of solution wouldn't work in A2K as well.

Posting Permissions

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