Display only fields with value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I hide empty fields in a report? The table "Batch" which I'm using has the fields "City", "State" and "Province". Now when I pull a report I want the fields which have a value entered in them only to be displayed. That is; if for a particular record; the field "City" and "State" have values in them and the field "Province" is a blank then only "City" and "State" should be displayed and the field name "Province" with a blank value should not appear and the next record should immediately follow without leaving any space. In a nutshell only a field with value should appear on a report otherwise the field with no value should be hidden. Can anyone help?
 
Sahil

A couple approaches:

If there are no controls located to the right of the controls ("fields") you
wish to hide, you can set those fields' CanGrow and CanShrink properties to
"Yes". Controls located to the right in a report will prevent the
Grow/Shrink.

If all the fields are address-related, you could create a new control and
make its source a concatenation of all the address-related fields. That
way, if anything is missing, it isn't included. The expression would be
something like (actual syntax may vary):
= [YourCity] & ", " & [YourState] & [YourProvince]

A minor point... if there's no data, nothing will be display in the control.
Are you saying that you don't wish the label attached to the field to show?
 
Hi Boyce

Thanks for your help, yes you've got it correct, I even do not wish the label attached to the field to be shown when there is no value in the field. Morever these fields will be displayed one below the other and not side by side. I hope you can understand the requirement. Thanx in advance.
 
If I understand, you can change the labels to text boxes and set their
control sources to expressions like:
="City " + [CityField]
="State " + [StateField]
If the fields are null then the text boxes will be null and can shrink. Make
sure you don't allow the text boxes to grow because you don't want to see
the field values with the "caption" strings.

--
Duane Hookom
MS Access MVP


Sahil said:
Hi Boyce,

Thanks for your help, yes you've got it correct, I even do not wish the
Click to expand...
label attached to the field to be shown when there is no value in the field.
Morever these fields will be displayed one below the other and not side by
side. I hope you can understand the requirement. Thanx in advance.
 
Thanks Duane, I tried customizing the text boxes by changing the control source to the expressions you suggested but when I run the report, the text boxes display "#Error". Can you suggest what is the problem here?
 
I didn't want you customize existing text boxes. I suggested you leave your
existing text boxes and change your Labels into text boxes and set the
control sources as I suggested.

--
Duane Hookom
MS Access MVP


Sahil said:
Thanks Duane, I tried customizing the text boxes by changing the control
Click to expand...
source to the expressions you suggested but when I run the report, the text
boxes display "#Error". Can you suggest what is the problem here?
 
How do I change the Labels to Text Boxes? Also there seems to be no property like "Control Source" available for a Lable. Please advice
 
Select the label control and then menu Format|Change To and select Text Box.

--
Duane Hookom
MS Access MVP


Sahil said:
How do I change the Labels to Text Boxes? Also there seems to be no
Click to expand...
property like "Control Source" available for a Lable. Please advice
 
Select the label and then look through the Format menu. Once you have
changed the format of the control to a text box, then you will find a
control source.

--
Duane Hookom
MS Access MVP


Sahil said:
How do I change the Labels to Text Boxes? Also there seems to be no
Click to expand...
property like "Control Source" available for a Lable. Please advice
 
Thanks Duane, but I dont see anything as "Change To" under Format property of a Label. Please advise
 
I'm not sure what version of Access you are using but I think this has been
included in several recent versions. I can right-click a label and choose
"Change To" or I can select a label control and the select Format|Change To.

--
Duane Hookom
MS Access MVP


Sahil said:
Thanks Duane, but I dont see anything as "Change To" under Format property
Click to expand...
of a Label. Please advise
 
Thanks for your help Duane, I got it and this works fine. But the problem is it leaves space for the field which is blank. That is suppose only the field "Province" has a value and the the fields "City" and "State" are blank, then it leaves space for these fields and then displays the label/textbox "Province" at the bottom. Is there anyway whereby I can display the fields with values only without leaving any space between the fields which have no value.
 
By "between" do you mean horizontal or vertical? You can't realistically
shrink controls vertically if there are other controls to the left or right
of the controls you want to shrink.

--
Duane Hookom
MS Access MVP


Sahil said:
Thanks for your help Duane, I got it and this works fine. But the problem
Click to expand...
is it leaves space for the field which is blank. That is suppose only the
field "Province" has a value and the the fields "City" and "State" are
blank, then it leaves space for these fields and then displays the
label/textbox "Province" at the bottom. Is there anyway whereby I can
display the fields with values only without leaving any space between the
fields which have no value.
 
Since my fields are one below the other, what I meant was that if a particular field is blank, the one below it should be carried up and hence there should be no space at all between records. I hope this is clear. Thanks
 
If you
-change any labels to text boxes
-set control sources of "previous labels" to something like:
="Some Caption " + [PossiblyNullFieldForTheCaption]
-set the control to can shrink
-move the controls so that none of them overlap vertically
-make sure the field values are actually Null and not zero-length-strings
Then,
the controls will shrink.
If they don't, what have you done to trouble-shoot/test? Have you tried set
the border of the controls to a color so you could confirm they aren't
shrinking?

--
Duane Hookom
Microsoft Access MVP


Sahil said:
Since my fields are one below the other, what I meant was that if a
Click to expand...
particular field is blank, the one below it should be carried up and hence
there should be no space at all between records. I hope this is clear.
Thanks
 
Thanks Duane, the fields are actually shrinking (only if they are the ones in the same horizontal line). I think this serves the purpose and it is fine. But can you explain what do you mean by "make sure the field values are actually Null and not zero-length-strings". Thanks for your help
 
Null means there is no value in the field. ZLS are actual values of "" which
looks like null but behaves different. This should only bother you if you
have set default values to "" or run code that inserts "" into fields.

--
Duane Hookom
Microsoft Access MVP


Sahil said:
Thanks Duane, the fields are actually shrinking (only if they are the
Click to expand...
ones in the same horizontal line). I think this serves the purpose and it is
fine. But can you explain what do you mean by "make sure the field values
are actually Null and not zero-length-strings". Thanks for your help.
 
Back
Top