IIf and Trim

[Guest]

Hi. I have a snippet of sql that I'm trying to modify.

Here's my example: John Doe and Susie Anderson are spouses. I've been
listing their names using:
(IIf(Len(Trim([SO Name]) & "")=0,Trim([Member Name]),Trim([Member Name] & "
& " & [SO Name]))) AS JointName
(this allows singles to still have a JointName, which is their single name)

However, if Susie is PrimaryFamilyMember = Yes, I want the name order to be
Susie Anderson and John Doe.

I'm trying to figure out how to say If Contacts.PrimaryFamilyMember <>0,
have JointName be Trim([Member Name] & " & " & [SO Name]
otherwise JointName should be Trim([SO Name] & " & " & [Member Name]

I'd appreicate assistance navigating IIf. Thanks for your time.

 

[[MVP] S.Clark]

I don't care much for IIF.

I would use an Append query to write all of the Primary people into a temp
table. Then, gather the spouse data into some other columns. With the
report setup correctly, the primary will always be listed first.

 

[Tom Lake]

have JointName be Trim([Member Name] & " & " & [SO Name]
otherwise JointName should be Trim([SO Name] & " & " & [Member Name]

I'd appreicate assistance navigating IIf. Thanks for your time.

JointName = IIf(Contacts.PrimaryFamilyMember <>0, Trim([Member Name] & " & "
& [SO Name], Trim([SO Name] & " & " & [Member Name])

Tom Lake