Suppressing address lines

[Andreas]

I have the following scenario: I print an invoice which includes address
text boxes in the following order:

____________________________________
| CustName |
| AddrLine1 |
| AddrLine2 |
| PCode |
| City |
| Country |
|___________________________________|

As you can see, if AddressLine1 through PCode are empty, I will have a big
empty gap in this label. Those fields are used in textboxes. How can I
suppress the empty space so that City goes higher?

 

[Allen Browne]

Set the Can Shrink property of each text box to Yes.

Make sure the Can Shrink property of the (Detail?) Section stays as No.

 

[Andreas]

This is exactly how it currently is. I tried it extensively with several
reports and that doesn't work

 

[BruceM]

Are there any text boxes to either side of the ones you have listed?
Try adding shading to the text boxes to help you see which ones are
misbehaving.

 

[Andreas]

I just discovered that if I remove the box that I drew around this area, it
works. There is no "can shrink" property for the box drawing so it doesn't
let the text shrink. However, I need this box because this is supposed to be
a label. Is there any solution?

 

[Tom Lake]

I just discovered that if I remove the box that I drew around this area, it
works. There is no "can shrink" property for the box drawing so it doesn't
let the text shrink. However, I need this box because this is supposed to
be a label. Is there any solution?

Instead of drawing a box around it, why not just set the border to
something other than Transparent?

Tom Lake

 

[BruceM]

Each box would have a border in that case, which is probably not intended.
One idea is to concatenate the text. If an unbound text box
(txtFullAddress) is in the Detail section, the section's Print event could
be:
Me.txtFullAddress = Me.CustName & vbCrLf & Me.AddrLine1 & _
(vbCrLf + Me.AddrLine2) & (vbCrLf + Me.PCode) & _
Me.City & vbCrLf & Me.Country

vbCrLf is carriage return and line feed (i.e. move down and to the beginning
of the next line). Using the plus sign as the concatenation operator means
that the expression evaluates to Null if any part of it is null. For
instance, if AddrLine2 is null, (vbCrLf + Me.AddrLine2) is also null, so it
is in effect ignored. Same for PCode. The other fields could be treated
the same way, although I assume some of them will be included in every
record; you may not have to allow for the possibility that there is no
AddrLine1. The code above makes that assumption.

The concatenation can also occur in a query, except that Chr(13) & Chr(10)
take the place of vbCrLf. In a blank column in query design view:
FullAddress: Me.CustName & Chr(13) & Chr(10) & Me.AddrLine1 & (Chr(13) +
Chr(10) + Me.AddrLine2) & etc.

By substituting an = sign for FullAddress: the expression can be used as the
control source of an unbound text box. I personally think it's easier to
work with either the query or the code. In any case, txtFullAddress is set
to shrink or grow, as needed, and it has only one border.

 

[Andreas]

I use:
FullAddress: [QuotCust] & Chr(13) & Chr(10)+[CustAddress1] & Chr(13) &
Chr(10)+[CustAddress2] & Chr(13) & Chr(10) & [CustPostCode] & " "+[CustCity]

But that will replace every null value with a square box instead.

 

[BruceM]

You left out some parentheses. Try this:

FullAddress: [QuotCust] & Chr(13) & Chr(10) & [CustAddress1] & (Chr(13) +
Chr(10) + [CustAddress2]) & (Chr(13) + Chr(10) + [CustPostCode]) & (" " +
[CustCity])

Note that (Chr(13) + Chr(10) + [CustAddress2]) is surrounded by parentheses.
If CustAddress2 is null, the expression within the parentheses is null
because you are using the + sign rather than & as the concatenation operator
(combining things in this way is called concatenation, in case this is a
mystery term). There will not be a square box because there is no carriage
return. Same for PostalCode. Some values will be part of every record.
For instance, if it is a mailing address it will probably need name,
address, city, and state at the least, so those values don't need to be
handled in that way.

Contrast this to what you have done. The first Chr(13) & Chr(10) is going
to occur no matter what, because there is nothing to break the whole string
into smaller elements.

I use:
FullAddress: [QuotCust] & Chr(13) & Chr(10)+[CustAddress1] & Chr(13) &
Chr(10)+[CustAddress2] & Chr(13) & Chr(10) & [CustPostCode] & "
"+[CustCity]

But that will replace every null value with a square box instead.

Each box would have a border in that case, which is probably not
intended.
One idea is to concatenate the text. If an unbound text box
(txtFullAddress) is in the Detail section, the section's Print event
could be:
Me.txtFullAddress = Me.CustName & vbCrLf & Me.AddrLine1 & _
(vbCrLf + Me.AddrLine2) & (vbCrLf + Me.PCode) & _
Me.City & vbCrLf & Me.Country

vbCrLf is carriage return and line feed (i.e. move down and to the
beginning of the next line). Using the plus sign as the concatenation
operator means that the expression evaluates to Null if any part of it is
null. For instance, if AddrLine2 is null, (vbCrLf + Me.AddrLine2) is
also null, so it is in effect ignored. Same for PCode. The other fields
could be treated the same way, although I assume some of them will be
included in every record; you may not have to allow for the possibility
that there is no AddrLine1. The code above makes that assumption.

The concatenation can also occur in a query, except that Chr(13) &
Chr(10) take the place of vbCrLf. In a blank column in query design
view:
FullAddress: Me.CustName & Chr(13) & Chr(10) & Me.AddrLine1 & (Chr(13) +
Chr(10) + Me.AddrLine2) & etc.

By substituting an = sign for FullAddress: the expression can be used as
the control source of an unbound text box. I personally think it's
easier to work with either the query or the code. In any case,
txtFullAddress is set to shrink or grow, as needed, and it has only one
border.

 

[Andreas]

In that way I don't get the square but the empty lines are not suppressed. I
still get empty lines if one of those fields are blank

You left out some parentheses. Try this:

FullAddress: [QuotCust] & Chr(13) & Chr(10) & [CustAddress1] & (Chr(13) +
Chr(10) + [CustAddress2]) & (Chr(13) + Chr(10) + [CustPostCode]) & (" " +
[CustCity])

Note that (Chr(13) + Chr(10) + [CustAddress2]) is surrounded by
parentheses. If CustAddress2 is null, the expression within the
parentheses is null because you are using the + sign rather than & as the
concatenation operator (combining things in this way is called
concatenation, in case this is a mystery term). There will not be a
square box because there is no carriage return. Same for PostalCode.
Some values will be part of every record. For instance, if it is a mailing
address it will probably need name, address, city, and state at the least,
so those values don't need to be handled in that way.

Contrast this to what you have done. The first Chr(13) & Chr(10) is going
to occur no matter what, because there is nothing to break the whole
string into smaller elements.

I use:
FullAddress: [QuotCust] & Chr(13) & Chr(10)+[CustAddress1] & Chr(13) &
Chr(10)+[CustAddress2] & Chr(13) & Chr(10) & [CustPostCode] & "
"+[CustCity]

But that will replace every null value with a square box instead.

Each box would have a border in that case, which is probably not
intended.
One idea is to concatenate the text. If an unbound text box
(txtFullAddress) is in the Detail section, the section's Print event
could be:
Me.txtFullAddress = Me.CustName & vbCrLf & Me.AddrLine1 & _
(vbCrLf + Me.AddrLine2) & (vbCrLf + Me.PCode) & _
Me.City & vbCrLf & Me.Country

vbCrLf is carriage return and line feed (i.e. move down and to the
beginning of the next line). Using the plus sign as the concatenation
operator means that the expression evaluates to Null if any part of it
is null. For instance, if AddrLine2 is null, (vbCrLf + Me.AddrLine2) is
also null, so it is in effect ignored. Same for PCode. The other
fields could be treated the same way, although I assume some of them
will be included in every record; you may not have to allow for the
possibility that there is no AddrLine1. The code above makes that
assumption.

The concatenation can also occur in a query, except that Chr(13) &
Chr(10) take the place of vbCrLf. In a blank column in query design
view:
FullAddress: Me.CustName & Chr(13) & Chr(10) & Me.AddrLine1 & (Chr(13) +
Chr(10) + Me.AddrLine2) & etc.

By substituting an = sign for FullAddress: the expression can be used as
the control source of an unbound text box. I personally think it's
easier to work with either the query or the code. In any case,
txtFullAddress is set to shrink or grow, as needed, and it has only one
border.



I just discovered that if I remove the box that I drew around this
area, it works. There is no "can shrink" property for the box drawing
so it doesn't let the text shrink. However, I need this box because
this is supposed to be a label. Is there any solution?

Instead of drawing a box around it, why not just set the border to
something other than Transparent?

Tom Lake

 

[Andreas]

Got it:

FullAddress: ([QuotCust]) & (Chr(13)+Chr(10)+[CustAddress1]) &
(Chr(13)+Chr(10)+[CustAddress2]) & Chr(13)+Chr(10) & [CustPostCode] & ("
"+[CustCity])

Thanks very much.

Any idea how I can replace the (" "+ in order to also suppress the space
when needed?

You left out some parentheses. Try this:

FullAddress: [QuotCust] & Chr(13) & Chr(10) & [CustAddress1] & (Chr(13) +
Chr(10) + [CustAddress2]) & (Chr(13) + Chr(10) + [CustPostCode]) & (" " +
[CustCity])

Note that (Chr(13) + Chr(10) + [CustAddress2]) is surrounded by
parentheses. If CustAddress2 is null, the expression within the
parentheses is null because you are using the + sign rather than & as the
concatenation operator (combining things in this way is called
concatenation, in case this is a mystery term). There will not be a
square box because there is no carriage return. Same for PostalCode.
Some values will be part of every record. For instance, if it is a mailing
address it will probably need name, address, city, and state at the least,
so those values don't need to be handled in that way.

Contrast this to what you have done. The first Chr(13) & Chr(10) is going
to occur no matter what, because there is nothing to break the whole
string into smaller elements.

I use:
FullAddress: [QuotCust] & Chr(13) & Chr(10)+[CustAddress1] & Chr(13) &
Chr(10)+[CustAddress2] & Chr(13) & Chr(10) & [CustPostCode] & "
"+[CustCity]

But that will replace every null value with a square box instead.

Each box would have a border in that case, which is probably not
intended.
One idea is to concatenate the text. If an unbound text box
(txtFullAddress) is in the Detail section, the section's Print event
could be:
Me.txtFullAddress = Me.CustName & vbCrLf & Me.AddrLine1 & _
(vbCrLf + Me.AddrLine2) & (vbCrLf + Me.PCode) & _
Me.City & vbCrLf & Me.Country

vbCrLf is carriage return and line feed (i.e. move down and to the
beginning of the next line). Using the plus sign as the concatenation
operator means that the expression evaluates to Null if any part of it
is null. For instance, if AddrLine2 is null, (vbCrLf + Me.AddrLine2) is
also null, so it is in effect ignored. Same for PCode. The other
fields could be treated the same way, although I assume some of them
will be included in every record; you may not have to allow for the
possibility that there is no AddrLine1. The code above makes that
assumption.

The concatenation can also occur in a query, except that Chr(13) &
Chr(10) take the place of vbCrLf. In a blank column in query design
view:
FullAddress: Me.CustName & Chr(13) & Chr(10) & Me.AddrLine1 & (Chr(13) +
Chr(10) + Me.AddrLine2) & etc.

By substituting an = sign for FullAddress: the expression can be used as
the control source of an unbound text box. I personally think it's
easier to work with either the query or the code. In any case,
txtFullAddress is set to shrink or grow, as needed, and it has only one
border.



I just discovered that if I remove the box that I drew around this
area, it works. There is no "can shrink" property for the box drawing
so it doesn't let the text shrink. However, I need this box because
this is supposed to be a label. Is there any solution?

Instead of drawing a box around it, why not just set the border to
something other than Transparent?

Tom Lake

 

[Andreas]

Ok I got it again:
FullAddress: ([QuotCust]) & (Chr(13)+Chr(10)+[CustAddress1]) &
(Chr(13)+Chr(10)+[CustAddress2]) & Chr(13)+Chr(10) & [CustPostCode] &
(IIf(IsNull([CustPostCode]);"";" "))+[CustCity]

Thanks again.

Got it:

FullAddress: ([QuotCust]) & (Chr(13)+Chr(10)+[CustAddress1]) &
(Chr(13)+Chr(10)+[CustAddress2]) & Chr(13)+Chr(10) & [CustPostCode] & ("
"+[CustCity])

Thanks very much.

Any idea how I can replace the (" "+ in order to also suppress the space
when needed?

You left out some parentheses. Try this:

FullAddress: [QuotCust] & Chr(13) & Chr(10) & [CustAddress1] & (Chr(13) +
Chr(10) + [CustAddress2]) & (Chr(13) + Chr(10) + [CustPostCode]) & (" " +
[CustCity])

Note that (Chr(13) + Chr(10) + [CustAddress2]) is surrounded by
parentheses. If CustAddress2 is null, the expression within the
parentheses is null because you are using the + sign rather than & as the
concatenation operator (combining things in this way is called
concatenation, in case this is a mystery term). There will not be a
square box because there is no carriage return. Same for PostalCode.
Some values will be part of every record. For instance, if it is a
mailing address it will probably need name, address, city, and state at
the least, so those values don't need to be handled in that way.

Contrast this to what you have done. The first Chr(13) & Chr(10) is
going to occur no matter what, because there is nothing to break the
whole string into smaller elements.

I use:
FullAddress: [QuotCust] & Chr(13) & Chr(10)+[CustAddress1] & Chr(13) &
Chr(10)+[CustAddress2] & Chr(13) & Chr(10) & [CustPostCode] & "
"+[CustCity]

But that will replace every null value with a square box instead.



Each box would have a border in that case, which is probably not
intended.
One idea is to concatenate the text. If an unbound text box
(txtFullAddress) is in the Detail section, the section's Print event
could be:
Me.txtFullAddress = Me.CustName & vbCrLf & Me.AddrLine1 & _
(vbCrLf + Me.AddrLine2) & (vbCrLf + Me.PCode) & _
Me.City & vbCrLf & Me.Country

vbCrLf is carriage return and line feed (i.e. move down and to the
beginning of the next line). Using the plus sign as the concatenation
operator means that the expression evaluates to Null if any part of it
is null. For instance, if AddrLine2 is null, (vbCrLf + Me.AddrLine2)
is also null, so it is in effect ignored. Same for PCode. The other
fields could be treated the same way, although I assume some of them
will be included in every record; you may not have to allow for the
possibility that there is no AddrLine1. The code above makes that
assumption.

The concatenation can also occur in a query, except that Chr(13) &
Chr(10) take the place of vbCrLf. In a blank column in query design
view:
FullAddress: Me.CustName & Chr(13) & Chr(10) & Me.AddrLine1 & (Chr(13)
+ Chr(10) + Me.AddrLine2) & etc.

By substituting an = sign for FullAddress: the expression can be used
as the control source of an unbound text box. I personally think it's
easier to work with either the query or the code. In any case,
txtFullAddress is set to shrink or grow, as needed, and it has only one
border.



I just discovered that if I remove the box that I drew around this
area, it works. There is no "can shrink" property for the box drawing
so it doesn't let the text shrink. However, I need this box because
this is supposed to be a label. Is there any solution?

Instead of drawing a box around it, why not just set the border to
something other than Transparent?

Tom Lake

 

[BruceM]

Or:
FullAddress: ([QuotCust]) & (Chr(13) + Chr(10) + [CustAddress1]) &
(Chr(13) + Chr(10) + [CustAddress2]) & Chr(13) & Chr(10) &
([CustPostCode] + " ") & [CustCity]

It's probably a good practice to use the ampersand for concatenation unless
there is a specific need, such as described in this thread, to use the plus
sign.

Ok I got it again:
FullAddress: ([QuotCust]) & (Chr(13)+Chr(10)+[CustAddress1]) &
(Chr(13)+Chr(10)+[CustAddress2]) & Chr(13)+Chr(10) & [CustPostCode] &
(IIf(IsNull([CustPostCode]);"";" "))+[CustCity]

Thanks again.

Got it:

FullAddress: ([QuotCust]) & (Chr(13)+Chr(10)+[CustAddress1]) &
(Chr(13)+Chr(10)+[CustAddress2]) & Chr(13)+Chr(10) & [CustPostCode] & ("
"+[CustCity])

Thanks very much.

Any idea how I can replace the (" "+ in order to also suppress the
space when needed?

You left out some parentheses. Try this:

FullAddress: [QuotCust] & Chr(13) & Chr(10) & [CustAddress1] & (Chr(13)
+ Chr(10) + [CustAddress2]) & (Chr(13) + Chr(10) + [CustPostCode]) & ("
" + [CustCity])

Note that (Chr(13) + Chr(10) + [CustAddress2]) is surrounded by
parentheses. If CustAddress2 is null, the expression within the
parentheses is null because you are using the + sign rather than & as
the concatenation operator (combining things in this way is called
concatenation, in case this is a mystery term). There will not be a
square box because there is no carriage return. Same for PostalCode.
Some values will be part of every record. For instance, if it is a
mailing address it will probably need name, address, city, and state at
the least, so those values don't need to be handled in that way.

Contrast this to what you have done. The first Chr(13) & Chr(10) is
going to occur no matter what, because there is nothing to break the
whole string into smaller elements.

I use:
FullAddress: [QuotCust] & Chr(13) & Chr(10)+[CustAddress1] & Chr(13) &
Chr(10)+[CustAddress2] & Chr(13) & Chr(10) & [CustPostCode] & "
"+[CustCity]

But that will replace every null value with a square box instead.



Each box would have a border in that case, which is probably not
intended.
One idea is to concatenate the text. If an unbound text box
(txtFullAddress) is in the Detail section, the section's Print event
could be:
Me.txtFullAddress = Me.CustName & vbCrLf & Me.AddrLine1 & _
(vbCrLf + Me.AddrLine2) & (vbCrLf + Me.PCode) & _
Me.City & vbCrLf & Me.Country

vbCrLf is carriage return and line feed (i.e. move down and to the
beginning of the next line). Using the plus sign as the concatenation
operator means that the expression evaluates to Null if any part of it
is null. For instance, if AddrLine2 is null, (vbCrLf + Me.AddrLine2)
is also null, so it is in effect ignored. Same for PCode. The other
fields could be treated the same way, although I assume some of them
will be included in every record; you may not have to allow for the
possibility that there is no AddrLine1. The code above makes that
assumption.

The concatenation can also occur in a query, except that Chr(13) &
Chr(10) take the place of vbCrLf. In a blank column in query design
view:
FullAddress: Me.CustName & Chr(13) & Chr(10) & Me.AddrLine1 & (Chr(13)
+ Chr(10) + Me.AddrLine2) & etc.

By substituting an = sign for FullAddress: the expression can be used
as the control source of an unbound text box. I personally think it's
easier to work with either the query or the code. In any case,
txtFullAddress is set to shrink or grow, as needed, and it has only
one border.



I just discovered that if I remove the box that I drew around this
area, it works. There is no "can shrink" property for the box drawing
so it doesn't let the text shrink. However, I need this box because
this is supposed to be a label. Is there any solution?

Instead of drawing a box around it, why not just set the border to
something other than Transparent?

Tom Lake