skipping blank fields in a report

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 Q95390. 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.

Reply | Quote

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

-Kelley

Reply | Quote