Iff in address block

[Marie]

Using Access. I give up. What is wrong with this code. It
works fine except if the Title and Last name fields are
blank, it puts the First name on the line with the company
name.

=IIf(IsNull([fldAttentionTitle]),"",[fldAttentionTitle]
& " ") & IIf(IsNull([fldAttentionFN]),"",[fldAttentionFN]
& " ") & IIf(IsNull([fldAttentionMName]),"",
[fldAttentionMName] & " ") & IIf(IsNull
([fldAttentionLN]),"",[fldAttentionLN] & Chr(13) & Chr
(10)) & IIf(IsNull([fldAttentionPosition]),"",
[fldAttentionPosition] & Chr(13) & Chr(10)) & IIf(IsNull
([fldCompanyName]),"",[fldCompanyName]) & Chr(13) & Chr
(10) & IIf(IsNull([fldCompanyName2]),"",[fldCompanyName2]
& Chr(13) & Chr(10)) & IIf(IsNull([fldCompanyMailing]),"",
[fldCompanyMailing] & Chr(13) & Chr(10)) & IIf(IsNull
([fldCompanyMailing2]),"",[fldCompanyMailing2] & Chr(13) &
Chr(10)) & IIf(IsNull([fldCity]),"",[fldCity] & ", ") & IIf
(IsNull([fldState]),"",[fldState] & " ") & IIf(IsNull
([fldZip]),"",[fldZip] & Chr(13) & Chr(10) & IIf(IsNull
([fldCountry]),"",[fldCountry]))

Also, how do I get this code to put only Dear First name:
if I don't have a last name (in which case I would not
fill in the title field)?

=IIf(IsNull([fldAttentionTitle]),"","Dear " &
[fldAttentionTitle] & [fldAttentionLN] & ":")

Thanks so much for your help.
Marie

 

[Marshall Barton]

Using Access. I give up. What is wrong with this code. It
works fine except if the Title and Last name fields are
blank, it puts the First name on the line with the company
name.

=IIf(IsNull([fldAttentionTitle]),"",[fldAttentionTitle]
& " ") & IIf(IsNull([fldAttentionFN]),"",[fldAttentionFN]
& " ") & IIf(IsNull([fldAttentionMName]),"",
[fldAttentionMName] & " ") & IIf(IsNull
([fldAttentionLN]),"",[fldAttentionLN] & Chr(13) & Chr
(10)) & IIf(IsNull([fldAttentionPosition]),"",
[fldAttentionPosition] & Chr(13) & Chr(10)) & IIf(IsNull
([fldCompanyName]),"",[fldCompanyName]) & Chr(13) & Chr
(10) & IIf(IsNull([fldCompanyName2]),"",[fldCompanyName2]
& Chr(13) & Chr(10)) & IIf(IsNull([fldCompanyMailing]),"",
[fldCompanyMailing] & Chr(13) & Chr(10)) & IIf(IsNull
([fldCompanyMailing2]),"",[fldCompanyMailing2] & Chr(13) &
Chr(10)) & IIf(IsNull([fldCity]),"",[fldCity] & ", ") & IIf
(IsNull([fldState]),"",[fldState] & " ") & IIf(IsNull
([fldZip]),"",[fldZip] & Chr(13) & Chr(10) & IIf(IsNull
([fldCountry]),"",[fldCountry]))

Also, how do I get this code to put only Dear First name:
if I don't have a last name (in which case I would not
fill in the title field)?

=IIf(IsNull([fldAttentionTitle]),"","Dear " &
[fldAttentionTitle] & [fldAttentionLN] & ":")

Thanks so much for your help.
Marie

You only put the new line sequence in when the
fldAttentionLN is not null, you might want to try it like
this??

.. . . & IIf(IsNull([fldAttentionLN]),"",[fldAttentionLN]) &
IIf(IsNull([fldAttentionPosition]),"",[fldAttentionPosition])
& Chr(13) & Chr(10) & . . .

There is a way to do (Most?) of this with out using IIf that
may be a little easier to read. The idea is based on the
fact that when the + operator is used with text (instead of
numbers), it performs concatenation like & does, but with a
crucial difference in what happens when it has a Null value.

"string" & Null returns "string"
"string" + Null returns Null

With that in mind, you might find it easer to work with the
expression if you rewrote it something like this:

=([fldAttentionTitle] + " ") & ([fldAttentionFN] + " ") &
([fldAttentionMName] + " ") & [fldAttentionLN] &
[fldAttentionPosition] & Chr(13) & Chr(10) &
([fldCompanyName]) + Chr(13) + Chr(10)) & ([fldCompanyName2]
+ Chr(13) + Chr(10)) & ([fldCompanyMailing] + Chr(13) +
Chr(10)) & ([fldCompanyMailing2] + Chr(13) + Chr(10)) &
([fldCity] + ", ") & ([fldState] + " ") & [fldZip] &
(Chr(13) + Chr(10) + [fldCountry])