How to clear NULL controls on a report in MS Access

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
Hi

I have an issue with one report Ms Access 2016, this report is required NOT to show any controls with ZERO values or NULL (Blank), is there a better way to do it either with VBA or within the controls. Only controls with values need to show on the report.

Your assistance will be highly appreciated

Regards

Chris
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
A control cannot hide itself. You will have to use VBA for this - like:

Me!SomeControl.Visible = CBool(Nz(Me!SomeControl.Value, 0))

Open in new window

Hi

I want also the report shrink without showing blank space.

Regards

Chris
I do not want to see the following on a report:

(1) Zeros)
(2) Blank Space


I want to see only controls with values and a shrink-ed one

Regards

Chris
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If you hide the control in the Format event, won't it shrink?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Or try adjusting the height:

Me!SomeControl.Visible = CBool(Nz(Me!SomeControl.Value, 0))
Me!SomeControl.Height = FixedHeightOfControl * Nz(Me!SomeControl.Value, 0)

Open in new window

It does not shrink the white space is still there.

Regards

Chris
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Perhaps you could provide a demo?
Okay this appear to work :

=IIf(IsNull([TxtFieldName]),Null,"Label Caption Value")
or
=[txtFieldName] +"Label Caption Value"
 
Then set this text box to Can Shrink.


but I want to Zero value also how do I amend my code above

regards

Chris
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That could be:

=IIf(Nz([TxtFieldName],"0")="0",Null,"Label Caption Value")

Open in new window


or what does
I want to Zero value
mean, please?
Distinguished Expert 2017
Commented:
For a report, if all the bound controls of an entire line are null (make sure there is no overlap or this won't work), the line will shrink automatically WITHOUT CODE if you have set the can shrink/Can grow properties to Yes for both the controls AND the detail section in which they appear.   This even applies to attached labels despite the fact that they are not bound.  That is one of the uses of attaching labels to controls.  Who knew?  Horizontal shrinking is not possible without code.  Same advice goes for other sections but usually the need for shrinking rows is more likely to occur in the detail section.

Where this can be problematic is if the detail section has multiple lines of controls and you have left vertical space between the controls.  There is no way for Access to shrink this space since it doesn't contain any controls.  So, if you need this vertical shrinking feature, eliminate as much of your own vertical space as you can or you will still be able to see where the "blank" data would have been.  None of the suggested code solutions will get rid of this excess white space either.

Access is a Rapid Application Development (RAD) tool and has an enormous set of features that will do stuff for you without code provided you understand how to use them and how to find them.  Access help has gotten less and less useful over the past 20 years so it isn't necessarily easy to find descriptions of what all the various form and report properties and built in functions do.

A suggestion for those of you just learning Access and even for some who are experts - Assume that the Access developers thought of "that" and try to find how it might have been implemented.  Of course the more you know about Access, the easier it is to find useful features.  I am always amazed when I read a solution here and it turns out to be something that I never noticed was possible.  Access is so well thought out and designed that I would be honored to worship at the feet of the original design team.  They were an awesome group of people.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial