hiding unpopulated fields in a report

[itauditor]

I have multiple records in my database, of which different fields for each record are null. I want the report to show only fields for each record that are populated. For example, fields: a, b, and c for records one and two are 1, 3, null and null, 5, 7, respectively. How do I make the report show fields a and b for record one and fields b and c for record 2?? Any help would be greatly appreciated. Thanks.

 

[Rohdem]

You could probably do something with the 'on format' event of the detail section of the report. The code runs on each record in the detail...

if isnull(me.field1) then
me.field1.visible = false
else
me.field1.visible = true
end if

....etc
Mike Rohde
rohdem@marshallengines.com

 

[Quehay]

Mike's suggestion is good--make sure you reference the field rather than the textbox. Another option is to have an alternate message: in the text boxes on the form (you have to drag empty textboxes rather than try to change the recordsource of existing textboxes that were created by dragging field names onto report) use the following as recordsource:

[tt] =iif(nz([fieldname])="","N/A",[fieldname]) [/tt]

If you want the box to just go away if empty then Mike's solution is better--just don't confuse fieldname with txtbox name (null vs. .visible)

 

[Zorro1265]

I tried using Rohdems suggestion but I keep getting an error. Here is my code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Me.repLabResults) Then Me.repLabResults_Label.Visible = False
Else
Me.repLabResults_Label.Visible = True
End If
End Sub

the message I get is
compile error
else without if

I am a novice at code could someone point me in the right direction? I would really like to be able to use this.

 

[Rohdem]

It is possible to write an if statement on one line and not have an 'end if' statement....

if text1 = "hi" then msgbox(text1)

I think you need to move the 'then' part of your if statement to a new line...

If IsNull(Me.repLabResults) Then
Me.repLabResults_Label.Visible = False
Else
Me.repLabResults_Label.Visible = True
End If


Mike Rohde
rohdem@marshallengines.com

 

[cattan]

I had a similar problem. I used this code for my report and it worked! (I hadn't realized the "visible" property also takes away the bound label. Cool!) Thanks! However, it left a blank space where the field was. I guess that makes sense: just because the textbox is invisible doesn't mean it went away. But is there any possible way that the next field down could be pushed up into that position to eliminate the white spaces in the report? I too am new to Access and VBA. I think I'm learning more from this type of forum than I am my Access book!

Cathy

 

[Rohdem]

Experiment with the 'can grow' property of the text box. If you create a text box that looks on the screen like just a line, then Access will resize it accordingly if there is information in it.
Mike Rohde
rohdem@marshallengines.com

 

[itauditor]

I used a combination of the suggested solutions to find a workaround. First, I entered text (NA) in all blank fields**. Then coded the following:

if [field] = "NA" then
me.field.visible = False
else
me.field.visible = True.
end if

Next, as Mike said, make your text boxes a height of .01 and move them as close together as possible. If you use the 'can grow' property (yes), then they will expand as needed.

The same can be done for labels, except it is my experience that labels can't "grow". So, to get rid of white space you have to change labels to text boxes, which opens up a whole new can of worms. If you need more detail on this, or have another solution, let me know.

**If you chose to try this method, program your table to have a default value of NA so you don't have to populate all blank fields by hand...

 

[steph22]

Thanks for the good ideas! I have a similar problem, but I need to do this on a Form rather than a report. I tried using the "Can Grow" Property on a text box, but couldn't get it to work. Does anyone know if this will work on a Form?

 

[emzadi]

I have tried this method, and i get rid of white space, except my fields tend to "grow" upwards instead of downwards, thereby knocking the line out of alignment. Ideas? Susan M. Wagner
LAPELS
susanw@lapels.com

 

[itauditor]

The "can grow" does work on the form. But my experience isthat you can only see it in action when you print the form...