Blank Fields in reports

[Jean]

I am trying to put together some reports in Access and I want to include a
Name and Address section. My name and address fields in the tables allow
for multiple line addresses and therefore may have blank lines. When I use
the full complement of fields in my report I have gaps where there are blank
fields in the table.

Can anyone advise how I can suppress blank fields and/or move lines up if
the previous line is blank?

Also, I would like to insert fields in a text box to neaten up some of the
information - can anyone advise if this is possible and how I achieve it?

Thank you

Jean

 

[Marshall Barton]

I am trying to put together some reports in Access and I want to include a
Name and Address section. My name and address fields in the tables allow
for multiple line addresses and therefore may have blank lines. When I use
the full complement of fields in my report I have gaps where there are blank
fields in the table.

Can anyone advise how I can suppress blank fields and/or move lines up if
the previous line is blank?

Also, I would like to insert fields in a text box to neaten up some of the
information - can anyone advise if this is possible and how I achieve it?

The standard way to "squeeze" out the blank lines in
multi-line address is to set both the detail section and the
text box's CanShrink property to Yes. This will only work
if each address text box is in it own "horizontal band".

If that won't work in your situation, then you can use a
single text box for all the address lines by using an
expression something like:

=[namefield] & (Chr(13) + Chr(10) + addr1) & (Chr(13) +
Chr(10) + addr2)

That works because the + used as a concatenation operator
propagates Null value and & does not.

I don't understand what you mean by "insert fields in a text
box". If it's just concatenating multiple fields into a
string in a text box expression, then sure you can do that
as my example above demonstrates. If you mean something
else, please explain more about what you're trying to do.

 

[Jean]

No further explanation required - you answered my question perfectly -
thanks for your help

Regards

Jean

I am trying to put together some reports in Access and I want to include a
Name and Address section. My name and address fields in the tables allow
for multiple line addresses and therefore may have blank lines. When I
use
the full complement of fields in my report I have gaps where there are
blank
fields in the table.

Can anyone advise how I can suppress blank fields and/or move lines up if
the previous line is blank?

Also, I would like to insert fields in a text box to neaten up some of the
information - can anyone advise if this is possible and how I achieve it?

The standard way to "squeeze" out the blank lines in
multi-line address is to set both the detail section and the
text box's CanShrink property to Yes. This will only work
if each address text box is in it own "horizontal band".

If that won't work in your situation, then you can use a
single text box for all the address lines by using an
expression something like:

=[namefield] & (Chr(13) + Chr(10) + addr1) & (Chr(13) +
Chr(10) + addr2)

That works because the + used as a concatenation operator
propagates Null value and & does not.

I don't understand what you mean by "insert fields in a text
box". If it's just concatenating multiple fields into a
string in a text box expression, then sure you can do that
as my example above demonstrates. If you mean something
else, please explain more about what you're trying to do.

 

[Guest]

Could you explain the following:
"That works because the + used as a concatenation operator propagates Null
value and & does not."
Are you saying that if Address1 is blank then Address2 will be directly
below Name rather than there being a space? If I am concatenating Address1,
Address2, and CityStateZip, and if Address2 is blank, then I want
CityStateZip to appear directly below Address1. I have used some rather
elaborate IIf statements to prevent a space where Address2 would have been,
but your way looks a whole lot cleaner and easier to write.

I am trying to put together some reports in Access and I want to include a
Name and Address section. My name and address fields in the tables allow
for multiple line addresses and therefore may have blank lines. When I use
the full complement of fields in my report I have gaps where there are blank
fields in the table.

Can anyone advise how I can suppress blank fields and/or move lines up if
the previous line is blank?

Also, I would like to insert fields in a text box to neaten up some of the
information - can anyone advise if this is possible and how I achieve it?

The standard way to "squeeze" out the blank lines in
multi-line address is to set both the detail section and the
text box's CanShrink property to Yes. This will only work
if each address text box is in it own "horizontal band".

If that won't work in your situation, then you can use a
single text box for all the address lines by using an
expression something like:

=[namefield] & (Chr(13) + Chr(10) + addr1) & (Chr(13) +
Chr(10) + addr2)

That works because the + used as a concatenation operator
propagates Null value and & does not.

I don't understand what you mean by "insert fields in a text
box". If it's just concatenating multiple fields into a
string in a text box expression, then sure you can do that
as my example above demonstrates. If you mean something
else, please explain more about what you're trying to do.

 

[Marshall Barton]

The key to my statement is summarized by:

string & Null results in string
and
string + Null results in Null

combining those basic rules into a more elaborate expression
like:

Name & (Chr(13) + Chr(10) + Address1) & (Chr(13) + Chr(10) +
Address2) & Chr(13) + Chr(10) & CityStateZip

If Address1 is Null, that null will propagate through the
subexpression in the parenthesis to reduce the above to:

Name & Null & (Chr(13) + Chr(10) + Address2) & Chr(13) &
Chr(10) & CityStateZip

Now the & between Name and Null will not propagate the Null,
so the next step in the evaluation is:

Name & (Chr(13) + Chr(10) + Address2) & Chr(13) & Chr(10) &
CityStateZip

The same thing will happen if Address2 is Null.
If Address 2 is not Null, then the CR and LF will be
concatenated to Address2 and that will be concatenated to
Name and the remaining terms to produce the final result:

Name
Address2
CityStateZip

Be careful to keep the differences between Null, a ZLS (Zero
Length String) and a string that only contains one or more
Space characters, all of which will appear as "blank" on the
screen. The above discussion only applies to Null.
--
Marsh
MVP [MS Access]

Could you explain the following:
"That works because the + used as a concatenation operator propagates Null
value and & does not."
Are you saying that if Address1 is blank then Address2 will be directly
below Name rather than there being a space? If I am concatenating Address1,
Address2, and CityStateZip, and if Address2 is blank, then I want
CityStateZip to appear directly below Address1. I have used some rather
elaborate IIf statements to prevent a space where Address2 would have been,
but your way looks a whole lot cleaner and easier to write.

The standard way to "squeeze" out the blank lines in
multi-line address is to set both the detail section and the
text box's CanShrink property to Yes. This will only work
if each address text box is in it own "horizontal band".

If that won't work in your situation, then you can use a
single text box for all the address lines by using an
expression something like:

=[namefield] & (Chr(13) + Chr(10) + addr1) & (Chr(13) +
Chr(10) + addr2)

That works because the + used as a concatenation operator
propagates Null value and & does not.

I don't understand what you mean by "insert fields in a text
box". If it's just concatenating multiple fields into a
string in a text box expression, then sure you can do that
as my example above demonstrates. If you mean something
else, please explain more about what you're trying to do.

 

[Guest]

Thanks. Sorry it took me so long to get back to you. Work schedule has
taken me elsewhere these past few days. I really appreciate the explanation.
I had been using IIf in a calculated query field to accomplish similar
results, but I could have simplified the whole thing greatly with this
technique. In effect it seems to be like the difference between multiplying
something by zero and adding zero to something.

The key to my statement is summarized by:

string & Null results in string
and
string + Null results in Null

combining those basic rules into a more elaborate expression
like:

Name & (Chr(13) + Chr(10) + Address1) & (Chr(13) + Chr(10) +
Address2) & Chr(13) + Chr(10) & CityStateZip

If Address1 is Null, that null will propagate through the
subexpression in the parenthesis to reduce the above to:

Name & Null & (Chr(13) + Chr(10) + Address2) & Chr(13) &
Chr(10) & CityStateZip

Now the & between Name and Null will not propagate the Null,
so the next step in the evaluation is:

Name & (Chr(13) + Chr(10) + Address2) & Chr(13) & Chr(10) &
CityStateZip

The same thing will happen if Address2 is Null.
If Address 2 is not Null, then the CR and LF will be
concatenated to Address2 and that will be concatenated to
Name and the remaining terms to produce the final result:

Name
Address2
CityStateZip

Be careful to keep the differences between Null, a ZLS (Zero
Length String) and a string that only contains one or more
Space characters, all of which will appear as "blank" on the
screen. The above discussion only applies to Null.
--
Marsh
MVP [MS Access]

Could you explain the following:
"That works because the + used as a concatenation operator propagates Null
value and & does not."
Are you saying that if Address1 is blank then Address2 will be directly
below Name rather than there being a space? If I am concatenating Address1,
Address2, and CityStateZip, and if Address2 is blank, then I want
CityStateZip to appear directly below Address1. I have used some rather
elaborate IIf statements to prevent a space where Address2 would have been,
but your way looks a whole lot cleaner and easier to write.

Jean wrote:
I am trying to put together some reports in Access and I want to include a
Name and Address section. My name and address fields in the tables allow
for multiple line addresses and therefore may have blank lines. When I use
the full complement of fields in my report I have gaps where there are blank
fields in the table.

Can anyone advise how I can suppress blank fields and/or move lines up if
the previous line is blank?

Also, I would like to insert fields in a text box to neaten up some of the
information - can anyone advise if this is possible and how I achieve it?

The standard way to "squeeze" out the blank lines in
multi-line address is to set both the detail section and the
text box's CanShrink property to Yes. This will only work
if each address text box is in it own "horizontal band".

If that won't work in your situation, then you can use a
single text box for all the address lines by using an
expression something like:

=[namefield] & (Chr(13) + Chr(10) + addr1) & (Chr(13) +
Chr(10) + addr2)

That works because the + used as a concatenation operator
propagates Null value and & does not.

I don't understand what you mean by "insert fields in a text
box". If it's just concatenating multiple fields into a
string in a text box expression, then sure you can do that
as my example above demonstrates. If you mean something
else, please explain more about what you're trying to do.