Calculated Field to Display a Name

[ProvPC]

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?

[ranman256]

you dont have to put all that IIF into the text box.
you can put a similar version in the ON CURRENT and AFTERUPDATE events to build the field
and have lots more code control....

sub form_onCurrent()
BuildName
end sub

sub txtLastName_Afterupdate()
BuildName
end sub


sub txtFirsttName_Afterupdate()
BuildName
end sub

sub txtSpouseName_Afterupdate()
BuildName
end sub


sub BuildName()
If ISNULL([SpouseLastName]) then                 'dont assemble the spouse fields
    vSpouse = ""
else
  vSpouse =" " & [SpouseFirstName]) & "   " [SpouseMiddleName]) "  " & [SpouseLastName]
endif

     'assemble both names 
vMain =[FirstName]) & "   " [MiddleName]) "  " & [LastName]
txtBox = vMain & vSpouse
end sub

[ProvPC]

Thanks for the tip - I'll give this a try!

I'm absolutely no expert in such things, but I'm not seeing where the script avoids adding the Spouse's last name if it is the same as the HoH's last name? That's the part where I fell down when trying to do things with my calculated field. (It could also be I'm just not smart enough to see how it's working, so I'll give it a try anyway!)

Thank you!

[ProvPC]

I thought I'd close this out by saying that I (sadly) couldn't quite figure out how to make the above work, so I took a less wise approach, and simply added an additional field to the table that compared last names, leaving the spouse's last name blank if it is the same as the head of household, and then used that result in my calculated name field. It's not pretty, and I'm sure your way is better, but it's an approach I understand and could make function. Thanks for your input!