How to clear NULL controls on a report in MS Access

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
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
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.

Gustav BrockCIOCommented:
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

0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Hi

I want also the report shrink without showing blank space.

Regards

Chris
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Gustav BrockCIOCommented:
If you hide the control in the Format event, won't it shrink?
0
Gustav BrockCIOCommented:
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

0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
It does not shrink the white space is still there.

Regards

Chris
0
Gustav BrockCIOCommented:
Perhaps you could provide a demo?
0
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
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
0
Gustav BrockCIOCommented:
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?
0
PatHartmanCommented:
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.
0

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
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.