[Webinar] Streamline your web hosting managementRegister Today


Hiding fields in a report if null

Posted on 1999-10-24
Medium Priority
Last Modified: 2010-08-05
My table has many fields used for costing.
I want to be able to print an invoice (report) automatically
but I do not want to display it if the value of the field is zero or null.
How can I do this?
Question by:faris

Accepted Solution

Stefaan earned 200 total points
ID: 2154746
Hi, isn't it enough to check the value of the edit control on your report ? This should be done in the control's band OnPrint event.

eg.  If you have a control MyNumber on the Detail Band your Detail Band's onPrint event should look like :

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  If Me.MyNumber = 0 Or Me.MyNumber = Null Then Me.MyNumber.Visible = False
End Sub


Expert Comment

ID: 2165456
I wouldn't doubt if you could cycle through the fields on the report's OnOpen event, and hide the field if it is empty.

Rough example:

Sub MyReport_OnOpen()
  If FieldName < 1 Then
    FieldName.Visible = False
    FieldName.Visible = True
End Sub

I've done this before I believe and think it works okay..


Expert Comment

ID: 2173634
Greetings, faris,

Stefaans answer and EvanLs comment are both correct, so you have to choose according to the function you need. Do you wish to display the field for some records, and not for others, go with Stefaans answer. Do you wish to hide the entire column of fields for all records based on the null criteria of a single record, then EvanLs comment is your solution.

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


Expert Comment

ID: 2173920

You are correct.  If there are only some cases where the field shouldn't be displayed, then my answer will be the best one, though in the other case it will also work.

Best regards,


Expert Comment

ID: 2173947
Even better solution, Faris:

In this case, I put this code right into the report text field's Control Source.

If the field called "PENum" is null, I make the field's value be, "No PE Number Provided."  If there IS a value, the value in the database is put in the textbox.  You could do the same thing, but instead of writing like I did, "No PE Number Provided," you could simply make the field "" (blank, or empty).

Here's the code:

=IIf([PENum] Is Null,"No PE Number Provided",[PENum])

This is by far the easiest way to accomplish what you want to do.  No VB module required.

Try it!  It works great!


Expert Comment

ID: 2173955
Whoops.. Forgot to mention, that this method hides individual text fields in a report, NOT an entire column.  I believe this is what your original question was in the first place..

Expert Comment

ID: 2209791
An eay way to deal with nulls is to convert them using the Nz function.

In your report, you could use the following as the control source, where, as an example, the field Age is to be displayed (or not displayed)


If Age is null, Nz will convert it to 0
other wise it evaluates to the number in the field

Any 0 (or null) will then be displayed as an empty string, while any other value will be displayed.

You can use this approach anywhere else you want to convert a null; just replace 0 (in the example above) to whatever you want null to be replaced.

This is very useful in SQL lookups, where nulls generate run time errors.

Expert Comment

ID: 6836320
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

Thank you,
Moderator @ Experts Exchange

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question