×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

hiding unpopulated fields in a report

hiding unpopulated fields in a report

hiding unpopulated fields in a report

(OP)
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.

RE: hiding unpopulated fields in a report

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

RE: hiding unpopulated fields in a report

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:

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

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)

RE: hiding unpopulated fields in a report

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.

RE: hiding unpopulated fields in a report

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

RE: hiding unpopulated fields in a report

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

RE: hiding unpopulated fields in a report

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

RE: hiding unpopulated fields in a report

(OP)
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...

RE: hiding unpopulated fields in a report

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?

RE: hiding unpopulated fields in a report

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

RE: hiding unpopulated fields in a report

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close