building a string in a public function

[Kathy R.]

Using Access 2007

I am trying to build a couple of strings from the following
tables/fields. Is it possible to do with this table structure, or would
it be easier to add a couple of fields to the tblFamily like FamTitle
(Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to
be redundant data though.

These are strings that I will use often and in various forms and reports
so I would like to put them into a public function, but I’m not familiar
with how to do that. I figure my first step is to build the strings
properly. Your help would be very much appreciated!

Kathy R.

tblFamily
FamID (primary key)
FamLastName

tblIndividual
IndID (primary key)
InFamID (foreign key)
InTitleID (foreign key)
FirstName
ContactStatus (primary contact, secondary contact, child, other)

tlkpTitle
TitleID (primary key)
Title (Mr., Mrs., Ms., Dr., etc.)

The strings I would like to build:

For each tblIndividual.InFamID (or would it be better to reference the
tblFamily.FamID?)

[Title of primary contact] & “ “ & [Title of secondary contact if
present] & “ “ & [FirstName of primary contact] & “ “ & [FamLastName]
Mr. and Mrs. John Doe (if there is a primary and secondary contact)
Mr. Bob Jones (if there is only a primary contact)

AND by first name instead of title:

[FirstName of primary contact] & “ “ & [FirstName of secondary contact
if present] & “ “ & [FamLastName]
John and Jane Doe (if there is a primary and secondary contact)
Bob Jones (if there is only a primary contact)

 

[Brian]

Try
FullName: [Title of primary contact] & iif([Title of secondary contact] <>
Null," " & [Title of secondary contact],"") ...