Append mail names based on whether 1 or two recipients and if marr

[fishy]

I have a table that can have two names on the various fields.

What I want to achieve is like this:

R Upson
R & A Upson
R Upson & A Other

I have set up the first part but didnt know where to go on the 2nd part:

Shortname:
IIf([Nf_Acct_Holders]=1,IIf([Customer1_FirstName]="",[Customer1_Surname],Left([Customer1_FirstName],1) & " " & [Customer1_Surname]),"XYZ")

The table source is from a table so I could create a field that runs a macro
to paste the data into the field if this is easier?

 

[Allen Browne]

There's not a simple, practical solution for this, particularly if you
actually need to include titles as well.

Nested IIf() expressions probably won't be powerful enough. It would be
possible to write a function that accepts the various fields, and works
through a series of scenarios to build the desired string. But there would
probably be scenarios this automated approach won't handle correctly (e.g.
where Mr Other has no known initial.)

Therefore you probably want to create an Addressee field in your table to
hold the results. Call the function in the AfterUpdate event of all the text
boxes that it depends on, and assign the function result to the Addressee
field. This automates the process, but allows the user to override it and
type in exactly what they want when the automated results are less than
desirable.

Strictly, having repeating names in the one record like that is not
normalized. You may well find that this proves to be an inadequate approach.
For example, if you had to receipt *one* of the persons (whoever actually
paid) in this convoluted record, you couldn't do it.

 

[fishy]

Had to compile on form as a series of iif's and strings which appended to a
field in the end as took too long to try and fix.