IIf and Trim

G

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.
 
M

[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.
 
T

Tom Lake

I'm trying to figure out how to say If Contacts.PrimaryFamilyMember said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top