Display only fields with value

S

Sahil

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?
 
J

Jeff Boyce

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?
 
S

Sahil

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

Duane Hookom

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

Sahil

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?
 
D

Duane Hookom

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?
 
S

Sahil

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
 
D

Duane Hookom

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
 
D

Duane Hookom

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
 
Top