Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

722 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