Hi all,

I am in the process of creating a database for my local Model A Ford Club and producing a Club Roster which I hope to publish this weekend...

I have a membership form into which I enter membership data including:

MemberFirstName
MemberLastName
SpouseFirstName
SpouseLastName

Example names:

John Smith (single member)
David & Mary Jones (married - same last name)
William Penn & Denise Stone (couple with different last names)

In my "members" report my code so far:

=[MemberFirstName] & IIf(Not IsNull([SpouseLastName])=True," " & [MemberLastName] & " " & "&" & " " & [SpouseFirstName] & " " & [SpouseLastName]," " & [MemberLastName]) & IIf(IsNull([SpouseLastName])=True," " & "&" & " " & [MemberLastName],"")

Works great for William Penn & Denise Stone

but...

John Smith comes out: John Smith & Smith
David & Mary Jones is David Jones & Jones

I've searched and my head is spinning at this point, can anyone please help me get my report to display correctly?

Is IIf too limited to take into account all the conditions?

Thanks,

Jay

Try This:

=[MemberFirstName] & IIf([MemberLastName]<>Nz([SpouseLastName])," " & [MemberLastName],"") & IIf(Nz([SpouseFirstName])<>""," & " & [SpouseFirstName] & " " & [SpouseLastName],"")

Rich,

=[MemberFirstName] & IIf([MemberLastName]<>Nz([SpouseLastName])," " & [MemberLastName],"") & IIf(Nz([SpouseFirstName])<>""," & " & [SpouseFirstName] & " " & [SpouseLastName],"")

Thanks for the assist! I plugged it in and tested results were:

John Smith (single member) - displays correctly

William Penn & Denise Stone (couple with different last names) - displays correctly

David & Mary Jones (married - same last name) - displays as:

David Jones & Mary (looking to display as: David & Mary Jones).

I'll try noodleing, but need to study and understand the logic of it (I am very new to this!)

Hope to hear back and again thank you.

Jay

I tested Rich's query here and it seems to work fine.

check this sample mdb

OK, I retested Rich's statement - if I always populate the SpouseLastName box then all 3 examples display correctly.

So question is, do I populate SpouseLastName all the time, or can this statement be modified to work when the SpouseLastName box is left empty because the spouse's last name matches the member's last name?

razorking, the statement is being used to display the "friendly name" format of my members in a text box on my form and as the name format in my car roster report, not a querie. Does that make a difference?

Also, if I may complicate things a bit further...

I also have a membership roster that displays the members' names (subgrouped alphabetically), addresses, phone numbers, & email adresses. In this report I'd like the name format to be such:

Smith, John (single member)
Jones, David & Mary (married - same last name)
Penn, William & Stone, Denise (couple with different last names)

Cheers,

Jay

Originally when I wrote the expression I did not realize that the spouse's last name could be null if it was the same as the member's last name. This was a tricky one with nested IIf's...

=[MemberFirstName] & IIf([MemberLastName]<>Nz([SpouseLastName]),IIf(Nz([SpouseFirstName])<>"" And Nz([SpouseLastName])="",""," " & [MemberLastName]),"") & IIf(Nz([SpouseFirstName])<>""," & " & [SpouseFirstName] & " " & IIf(Nz([SpouseLastName])="",[MemberLastName],[SpouseLastName]),"")

As far as the other format, I would think it's just a variation of the above expression but it's late and my brain is empty for now. :D

Rich - Plugged the string in this morning and it worked like a charm! Thanks!

I'm now trying to hack it to get that

MemberLastName, MemberFirstName
MemberLastName, MemberFirstName & SpouseFirstName
MemberLastName/SpouseLastName, MemberFirstName & SpouseFirstName

format...

When you build a statement like you supplied above, how do you go about working it? Do you start with an english language view of what you're trying to accomplish? Or I guess I'm asking how do you first diagram the logic needed to build the statement?

Thanks,

Jay

=[MemberLastName] & "," & IIf([MemberLastName]<>Nz([SpouseLastName]),IIf(Nz([SpouseFirstName])<>"" And Nz([SpouseLastName])="",""," " & [MemberFirstName]),"") & IIf(Nz([SpouseFirstName])<>""," & " & [SpouseFirstName] & " " & IIf(Nz([SpouseLastName])="",[MemberLastName],[SpouseLastName]),"")

Gets me the single member to display correctly
ie John Smith (single member) displays as Smith, John

But

(married - same last name) David & Mary Jones displays as:
Jones, & Mary Jones (should be: Jones, David & Mary)

(couple with different last names) William Penn & Denise Stone displays as:
Penn, William & Denise Stone (hoping for: Penn/Stone, William & Denise although Penn, William & Stone, Denise will do if it's easier :D )

I know it's all in the ordering of the above string with a little modified text formatting, my head is spinning trying to work through the logic of it all.

Thanks again everyone.

Jay

Another tricky one but here it is:

=[MemberLastName] & ", " & [MemberFirstName] & IIf(Nz([SpouseFirstName])<>""," & " & IIf(Nz([SpouseLastName])<>"",IIf([MemberLastName]=[SpouseLastName],"",[SpouseLastName] & ", "),"") & [SpouseFirstName],"")

When I wrote these expressions, I first designed it as code in a function using If...Then...Else statements so I could follow the steps one at a time. Once you start nesting IIf's it gets difficult to trace through.

Once I got the code working I just converted it to IIf's. This could have been done with a function just the same but since you were working with IIf, I decided to work my brain on that method.

Rich,

Worked great! Thank you thank you thank you...


I may have some other real posers later!