Expression Error

[newpete]

What is wrong with this expression please



=IIf(IsNull([Members Data]![His First Name]),Trim([Members Data]![Her First name]) & ' ' & Trim([Members Data]![Surname]),IIf(IsNull([Members Data]![Her First Name]),Trim([Members Data]![His First Name]) & ' ' & Trim([Members Data]![Surname]),Trim([Members Data]![His First name]) & ' ' & "&" & ' ' & Trim([Members Data]![Her First Name]) & ' ' & Trim([Members Data]![Surname])))

It looks at 2 fields in a table' His First Name' and 'Her First Name' if either is empty it gives the other and adds a 'Surname'. If neither is empty (Null) it ouputs both names and surname. Doesn't work. Outputs #name?.
I am new to this and am using 2013 on a windows 8 machine. What am i not seeing?
Thanks

[June7]

Try:

=Trim([His First Name] & IIf(IsNull([Her First Name]), "", IIf(Not IsNull([His First Name]), " and ", "")) & [Her First Name])) & " " & [Members Data]![Surname])

[newpete]

Try:

=Trim([His First Name] & IIf(IsNull([Her First Name]), "", IIf(Not IsNull([His First Name]), " and ", "")) & [Her First Name])) & " " & [Members Data]![Surname])

Thank you June, I appreciate the prompt reply and the neatness compared to my effort. Unfortunately I make it 2 too many closing parenthesis but I don't know which ones. This is the first time I have tried this. I'm ure you will know very rapidly.
Pete.

[June7]

=Trim([His First Name] & IIf(IsNull([Her First Name]), "", IIf(Not IsNull([His First Name]), " and ", "")) & [Her First Name] & " " & [Members Data]![Surname])

[newpete]

Hi June,
Just got back to this. Unfortunately still same result #Name ?. Am I putting it in the right place. Data>control source of a report. This is just the 1st piece of information for this report , they were entered from an old excel list and edited and full of rubbish and needs expanding. Table and associated form are oK.
Pet

[June7]

Report does not have ControlSource, textbox has ControlSource, report has RecordSource.

Expression goes in textbox ControlSource or on the Field row of query.

#NAME means Access can't find field referenced in expression. I forgot to remove a table reference prefix. Maybe that will make difference.

=Trim([His First Name] & IIf(IsNull([Her First Name]), "", IIf(Not IsNull([His First Name]), " and ", "")) & [Her First Name] & " " & [Surname])

[newpete]

Thanks June.
removed the table reference, still didn't work but found I had not put Members data as the record source in the report, so it then worked.

You have already taught me a lot, but am sure I will be back

Pete