concatenated address fields

[David Newmarch]

In a query called qryAddressBox I have the following expression to create a
concatenated field ("AddressBox") for use in various reports:

AddressBox: qryAddresses!Line1 & IIf(IsNull(qryAddresses!Line1),Null,Chr(13)
& Chr(10)) & qryAddresses!Line2 & IIf(IsNull(qryAddresses!Line2),Null,Chr(13)
& Chr(10)) & qryAddresses!Line3 & IIf(IsNull(qryAddresses!Line3),Null,Chr(13)
& Chr(10)) & qryAddresses!Line4 & IIf(IsNull(qryAddresses!Line4),Null,Chr(13)
& Chr(10)) & qryAddresses!Country

In the underlying query (qryAddresses) Line1, Line2, Line3, and Line4 are
fields from one table (tblClients) and Country is a field from a related
table (tblCountries).

My problem is that if the Line4 field is empty then the Country line doesn't
close up and I get a blank line where Line4 would be (no problem if there's
an empty field for any of the first three lines).

How do I get around this?

 

[BruceM]

How about:
AddressBox: (qryAddresses!Line1 + Chr(13) + Chr(10)) & (qryAddresses!Line2
+ Chr(13) + Chr(10)) & (qryAddresses!Line3 + Chr(13) + Chr(10)) &
(qryAddresses!Line4 + Chr(13) + Chr(10)) & qryAddresses!Country

Using + as the concatenation operator means that if any part of the
expression is null, the entire expression (within the parentheses) evaluates
to null.

I'm not sure just what is going on with the Line4 anomaly, but it could be
that it is an empty string rather than null. You could test by substituting
"Line1 is Null", "Line2 is Null", etc. (including the quotes) for Null in
your expression. If Line4 seems to be null, it should return the phrase
"Line 4 is Null". If it does not, try testing it for the presence of an
empty string ("").

Nz can be helpful in such cases (see Help for more information about Nz):
IIf(Nz(qryAddresses!Line1),"") = "","",qryAddresses!Line1 & chr(13) &
chr(10)) & etc.

By the way, I believe that using Null as a result (i.e. returning Null if
the expression evaluates to True) is an unreliable practice in some cases,
but I am unsure why.

 

[David Newmarch]

Thank for these suggestions, but I'm still stuck. Your first suggestion

AddressBox: (qryAddresses!Line1 + Chr(13) + Chr(10)) & (qryAddresses!Line2
+ Chr(13) + Chr(10)) & (qryAddresses!Line3 + Chr(13) + Chr(10)) &
(qryAddresses!Line4 + Chr(13) + Chr(10)) & qryAddresses!Country

gives exactly the same result as before, and the Line4 anomaly remains.
I then tried your second suggestion:

You could test by substituting
"Line1 is Null", "Line2 is Null", etc. (including the quotes) for Null in
your expression. If Line4 seems to be null, it should return the phrase
"Line 4 is Null".

This test works and tells me that , yes, Line4 *is* null.

I then tried your next suggestion:

Nz can be helpful in such cases (see Help for more information about Nz):
IIf(Nz(qryAddresses!Line1),"") = "","",qryAddresses!Line1 & chr(13) &
chr(10)) & etc.

I entered the following expression:

AddressBox: IIf(Nz(qryAddresses!Line1),"") = "","",qryAddresses!Line1 &
chr(13) & chr(10) & IIf(Nz(qryAddresses!Line2),"") = "","",qryAddresses!Line2
& chr(13) & chr(10) & IIf(Nz(qryAddresses!Line3),"") =
"","",qryAddresses!Line3 & chr(13) & chr(10) & IIf(Nz(qryAddresses!Line4),"")
= "","",qryAddresses!Line4 & chr(13) & chr(10) &
IIf(Nz(qryAddresses!Country),"") = "","",qryAddresses!Country

But this didn't work because I got an incorrect syntax error message, and I
can't tell what my error might be.

 

[John Spencer]

Try moving those parentheses around

AddressBox:
IIf(Nz(qryAddresses!Line1,"") = "","",qryAddresses!Line1 & chr(13) &
chr(10)) &
IIf(Nz(qryAddresses!Line2,"") = "","",qryAddresses!Line2 & chr(13) &
chr(10)) &
IIf(Nz(qryAddresses!Line3,"") = "","",qryAddresses!Line3 & chr(13) &
chr(10)) &
IIf(Nz(qryAddresses!Line4,"") = "","",qryAddresses!Line4 & chr(13) &
chr(10)) &
IIf(Nz(qryAddresses!Country,"") = "","",qryAddresses!Country)

 

[BruceM]

The problem with the Nz suggestion is that I gave you the wrong syntax.
There is an extra parentheses before the double quotes in the Nz expression.
It should be:
IIf(Nz(qryAddresses!Line1,"") = "",etc. rather than
IIf(Nz(qryAddresses!Line1),"") = "",etc.
However, there is still a puzzle here. In the first expression (with the +
signs) try substituting a text value (such as "Country" with the quotes) for
qryAddresses!Country. It should produce the word Country at the end of the
string. Is there still a space? If it has gone away, try AddressTest:
qryAddresses!Country, and see if there is anything unexpected in the field
by itself. You could also test the Country field by using it as the control
source of a text box on the form.

 

[David Newmarch]

Thanks John (and Bruce), that does the trick. The Line4 anomaly has finally
disappeared.