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]

Post by 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.
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.
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
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.
---
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]

Post by Ron Weiner

Post by 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.
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.
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
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.
---
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

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