Hi there!
I'm trying to create a calculated field for display purposes. I have a tabbed form with numerous fields that will require users to enter data from a variety of forms. In order to avoid some confusion, I've create a field at the top of the forms (visible on all tabs) that carries forward the names entered on the first form to avoid confusion.
The idea is pretty simple. Names are entered on the first tab of the form in varying combinations of first, last and middle names of either one person (if single) or two people (if married). The fields are HoHLastName, HoHFirstName, HoHMiddleName and SpouseLastName, SpouseFirstName, SpouseMiddleName. Of course, some persons are single, so there isn't any spouse names at all, and others don't have or didn't provide middle names (or provided initials instead), so sometimes those fields are empty in one or both people.
The format desired is this:
HoHLastName, HoHFirstName HoHMiddleName & SpouseFirstName SpouseMiddleName SpouseLastName (the SpouseLastName should only be present if it is different from the HoH). Like this:
Mouse, Mickey M & Minnie P
Duck, Donald & Daisy Diane
Dawg, Goofy
Etc.
I have a formula that worked splendidly for the most part:
IIf(IsNull([HoHLastName]),"",[HoHLastName])+IIf(IsNull([HoHFirst Name]),"",", ")+IIf(IsNull([HoHFirst Name]),"",[HoHFirst Name])+IIf(IsNull([HoHMiddleInitial]),""," ")+IIf(IsNull([HoHMiddleInitial]),"",[HoHMiddleInitial])+IIf(IsNull([SpouseFirstName]),""," & ")+IIf(IsNull([SpouseFirstName]),"",[SpouseFirstName])+IIf(IsNull([SpouseMiddleInitial]),""," ")+IIf(IsNull([SpouseMiddleInitial]),"",[SpouseMiddleInitial])+IIf(IsNull([SpouseLastName]),""," ")+IIf(IsNull([SpouseLastName]),"",[SpouseLastName])
This does exactly what I want it to do, except for the whole SpouseLastName only being present if different from the HoH thing. It displays the text as Mouse, Mickey M & Minnie P Mouse.
So I tried THIS:
IIf(IsNull([HoHLastName]),"",[HoHLastName])+IIf(IsNull([HoHFirstName]),"",", ")+IIf(IsNull([HoHFirstName]),"",[HoHFirstName])+IIf(IsNull([HoHMiddleName]),""," ")+IIf(IsNull([HoHMiddleName]),"",[HoHMiddleName])+IIf(IsNull([SpouseFirstName]),""," & ")+IIf(IsNull([SpouseFirstName]),"",[SpouseFirstName])+IIf(IsNull([SpouseMiddleName]),""," ")+IIf(IsNull([SpouseMiddleName]),"",[SpouseMiddleName])+IIf(IsNull([SpouseLastName]),""," ")+IIf([SpouseLastName]=[HoHLastName],"",[SpouseLastName]
This actually works, except that now the only time the name is displayed is if there IS a spouse. Single persons do not display at all. I suspect this is because it waits to see what's going to be in the SpouseLastName field before calculating the name - nothing there, nothing calculated - but I'm not bright enough to be sure.
So... is there a way to accomplish what I'm trying to do?