Joining two fields First name and spouse First name

[mdnikki]

Good morning,

I know how to join the fields but my issue is that some times there is no spouse first name.



We are trying to personalize an eblast with first name fields so if there is a first name and a spouse first name I want them to appear but if there is no spouse first name I want on the first name to appear.

Here is what I used.. .however when there is no spouse it shows Robert and

FN: Trim$([AAA13 DM2]![CnBio_First_Name]) & " and " & Trim$([AAA13 DM2]![CnSpSpBio_First_Name])

Any help is appreciated. I suppose I may need an iif statement in there somewhere.

Thanks

[orange]

Here's a short procedure to show the syntax

Sub testPlus()
    Dim n1(2, 1) As String
    n1(0, 0) = "John"
    n1(0, 1) = "Samantha"
    n1(1, 0) = "Tom"
    n1(1, 1) = ""   'Tom does not have a spouse
    n1(2, 0) = "Jeremiah"
    n1(2, 1) = "Danika"

    For i = 0 To 2
'
'The next line shows one syntax to do what you want
'
        Debug.Print n1(i, 0) + IIf(Len(n1(i, 1) & "") = 0, "", " and " & n1(i, 1))
'
        Next i
    End Sub

'there is always a first name, so move it to output
'The iif is saying
if the length of the spouse name is 0, then add nothing to the output
if the length of the spouse name is >0, then add the "and " & spouse name to the output

Here is the output

John and Samantha
Tom
Jeremiah and Danika

[mdnikki]

I apologize, I am not that familiar with code. Is there a way to do this in the query as a concatenated field with and iif statement.

Here's a short procedure to show the syntax

Sub testPlus()
    Dim n1(2, 1) As String
    n1(0, 0) = "John"
    n1(0, 1) = "Samantha"
    n1(1, 0) = "Tom"
    n1(1, 1) = ""   'Tom does not have a spouse
    n1(2, 0) = "Jeremiah"
    n1(2, 1) = "Danika"

    For i = 0 To 2
'
'The next line shows one syntax to do what you want
'
        Debug.Print n1(i, 0) + IIf(Len(n1(i, 1) & "") = 0, "", " and " & n1(i, 1))
'
        Next i
    End Sub

'there is always a first name, so move it to output
'The iif is saying
if the length of the spouse name is 0, then add nothing to the output
if the length of the spouse name is >0, then add the "and " & spouse name to the output

Here is the output

John and Samantha
Tom
Jeremiah and Danika

[orange]

Sure. Use this as a template

n1(i, 0) + IIf(Len(n1(i, 1) & "") = 0, "", " and " & n1(i, 1))

substitute
Trim$([AAA13 DM2]![CnBio_First_Name]) for n1(i,0)

and
Trim$([AAA13 DM2]![CnSpSpBio_First_Name]) for n1(i, 1)

good luck

[mdnikki]

sal: Trim$([AAA13 DM2]![CnBio_First_Name])+IIf(Len(Trim$([AAA13 DM2]![CnSpSpBio_First_Name]) & "")=0,""," and " & Trim$([AAA13 DM2]![CnSpSpBio_First_Name]))

Ok I think I did this correctly however for those with just a CNBio First name and no spouse I am getting a # Error message.

[orange]

And what is the error message and number?

[mdnikki]

Its just a #Error in the field where the first name should be...just on those that have a Bio First name with no spouse. The concatenated field of first name and spouse name appears correctly.

Thanks for you assistance and patience.

[orange]

Can you post a copy of your database --remove anything confidential/private?