Concatenate

[Debra Pringle]

Hello,

I am working on a query and cannot figure out how to add the word "and" to my scenario if the [Spouse] field is not empty.

My scenario is this:

Field names are [FName], [Spouse], [LName]

Always there is a name in FName and LName but sometimes there is no spouse.
IF [Spouse] is not Null, I need my formula to add the word "and" to the final answer. IF [Spouse] is Null, then just the [FName] [LName] should show.

This is the formula I have so far:

Full Name: [FName] & " " & IIf([Spouse] Is Not Null,[Spouse]) & " " & [LName]
I got my formula to work except for adding the word "and" with the formula I shared. What do I need to add to this formula to get the word "and" IF [Spouse] is not Null

End result should be:

IIf [Spouse] is not Null then this is what the answer should be: Ed and Debbie Pringle

IIf [Spouse] is Null then this is what the answer should be: Ed Pringle

This is done in a query in Microsoft Access.

[Ron Weiner]

> ---
> This email has been checked for viruses by AVG.
> http://www.avg.com

See if this works for you

SELECT [FName] & IIf(Len(nz([Spouse],''))>0,' ' & [Spouse],'') & ' ' &
[lastname] AS the name
FROM YourTable
WHERE whatever

Rdub

[Ron Weiner]

Ron Weiner pretended :

Opps mised the "and" Here is a Revision:

SELECT [FName] & IIf(Len(nz([Spouse],''))>0, ' and ' & [Spouse],'') & '
' & [Lname] AS TheConcatenatedName
FROM YourTable
WHERE Whatever

Should give:

Ed and Debbie Pringle where the spouse is defined

and:

Ed Pringle where ther was no spouse

Rdub

[Debra Pringle]

thank you Ron for your help! It works