Hiding fields in a report if null

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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..

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.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!


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,

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!

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..
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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.