Iif statment in query

[Tony Williams]

I want to create a query to be used in a mail merge with Word. I have a
table that has a fields for company name, contact's title,contact's initial
and contacts last name. There is always a contact name but not always a
company name. So when I'm building the address for a letter from the query I
want it to be something along these lines
1st line....................contact's title,contact's initial and contacts
last name
2nd line...................If the company name is blank, leave blank, but if
the company name is there show company name ( I don't want a blank line to
appear in the address
3rd Line................. address etc etc

I'm assuming I could use an IF statement in the query along the lines of
this
IIf (company is Null, isNull, company)

Am I right and where would I put the if statement
Thanks
Tony

 

[John Spencer]

Are you trying to build the entire address in one column of a query?
Your proposed solution doesn't do anything different than just returning the
value in Company.

SELECT (Title + " ") & Initial & " " & LastName & IIF(Company is not null,
Chr(13) & Chr(10) & Company) as Line1and2
, Address1
, City & " " & State & " " & PostalCode
FROM ...

 

[Tony Williams]

Hi John. No I was going to build the name, company and address in their own
columns but I was trying to arrive at an address which included the company
if there was one but left a space if there wasn't and then check the tick
box on the print option not to print blank lines. It was the If statement to
include the company name I was struggling with and where the IF statement
should go in the Query design view.
Tony

 

[John Spencer]

You can use

IIF(Company is Null, "", Company)

or

NZ(Company,"")

 

[Tony Williams]

Sorry John for wasting your time! I now find that the latest version of Word
has a default that doesn't print blank lines in mail merge addresses. You
live and learn!
Thanks again for your time
Tony