IIF - help in query & labels

[indyaries]

Greetings,

Have an address list with over 600 names, and I need to create mailing labels.

Have First Name, Last Name fields, with city, state zip. BUT...

There is also a field called SPOUSE. This field is NOT always populated.

I need a query that I'll use to create the mailing labels that will;

Have the FIRST NAME SPOUSE NAME LAST NAME

If the SPOUSE field is blank, I need the query to filter that out, so that the address lable will only show First Name and Last Name, without a blank space in between.

Also need the query to put the '&' sign in IF there is a spouse.

Example 1: John & Jane Doe
Example 2: John Doe

Any assistance will be appreciated!

Bob in Indy

 

[mresann]

Combine the Immediate If function with the IsNull function.

In the query field:

FullName: [FirstName] & " " & Iif(Not IsNull([SpouseName), "& " & [SpouseName] & " ", "") & [LastName]

If spouse is not present, return is "John Doe".

If spouse is present, return is "John & Jane Doe".

 

[indyaries]

mresann, thanks for the rapid reply.

I pasted your statement into the query grid, but it returned an error, stating that there were the wrong number of arguments.

What you wrote makes sense...I'm guessing that the punctuation may be off ??

Hoping for a reply

 

[mresann]

Yes, I missed a bracket to the right of Spousename, sorry.

Corrected:

FullName: [FirstName] & " " & Iif(Not IsNull([SpouseName]), "& " & [SpouseName] & " ", "") & [LastName]

 

[indyaries]

mresann,

Worked like a charm!! Thank you very much!!