1 Reply Latest reply on Jun 19, 2012 9:02 AM by philmodjunk

    Calculation for Letter head, address labels, etc. not working.

    StewartRitter

      Title

      Calculation for Letter head, address labels, etc. not working.

      Post

      So there are three different formats that we need:

      1. Full Name

      2. Letter Format for Husband and Wife

      3. Salutation Format

      And each of them needs to work in four different situations:

      -there is only a "husband"

      -there is only a "wife"

      -there is a husband and a wife, and they have the same last name

      -there is a husband and a wife, and they have different last names

      Appologies for the heteronormative labeling. I plan to change that once I have a working basic model. I would appreciate it if someone could look over my "code" for the calculation and point out where my errors are. The different formats currently only work when there is a husband and a wife and they have differnt last names.

      1. Full Name

       Case(

       

      IsEmpty(First Name Husband)=False & IsEmpty(First Name Wife)=False  & Last Name Husband = Last Name Wife; 

      Last Name Husband & ", " & First Name Husband & " and " & First Name Wife;

       

      IsEmpty(First Name Husband)=False & IsEmpty(First Name Wife)=False  & Last Name Husband  ≠  Last Name Wife; 

      Last Name Husband & ", " & First Name Husband & " and " & Last Name Wife & ", " & First Name Wife;

       

      IsEmpty(First Name Husband)=False & IsEmpty(First Name Wife) = True;

      Last Name Husband & ", " & First Name Husband;

       

      IsEmpty(First Name Husband) =True  & IsEmpty(First Name Wife) = False; 

      Last Name Wife & ", " & First Name Wife

      )

      2. Letter Format for Husband and Wife

      Case(

       

      IsEmpty(First Name Husband)=False & IsEmpty(First Name Wife)=False  & Last Name Husband = Last Name Wife;

      Title 1 & " and " & Title 2 & " " & First Name Husband & " " & Last Name Husband;

       

      IsEmpty(First Name Husband)=False & IsEmpty(First Name Wife)=False  & Last Name Husband  ≠ Last Name Wife;

      Title 1 &" " &  First Name Husband & " " & Last Name Husband & " and " & Title 2 & " " & First Name Wife & " " & Last Name Wife;

       

      IsEmpty(First Name Husband)=False & IsEmpty(First Name Wife) = True;

      Title 1 & " " & First Name Husband & " " & Last Name Husband;

       

      IsEmpty(First Name Husband) =True  & IsEmpty(First Name Wife) = False; 

      Title 2 & " " & First Name Wife & " " & Last Name Wife

      )

       

       

      3. Salutation Format

      Case(

      IsEmpty(First Name Husband)=False & IsEmpty(First Name Wife)=False;

      "Dear " & First Name Husband & " and " & First Name Wife & ",";

       

      IsEmpty(First Name Husband)=False & IsEmpty(First Name Wife) = True;

      "Dear" & First Name Husband & ",";

      IsEmpty(First Name Husband) =True  & IsEmpty(First Name Wife) = False; "Dear" & First Name Wife & ","

      )

        • 1. Re: Calculation for Letter head, address labels, etc. not working.
          philmodjunk

          The operators AND, & are two different operators. & Concatenates or "glues together" text. And is a logical operator that returns True if both terms on either side evaluate as True.

          1 FullName

          Case (  Not ( IsEmpty(First Name Husband) OR IsEmpty(First Name Wife) ) AND Last Name Husband = Last Name Wife;
                                                                         Last Name Husband & ", " & First Name Husband & " and " & First Name Wife;

                      Not ( IsEmpty(First Name Husband) OR IsEmpty(First Name Wife) ) AND Last Name Husband  ≠  Last Name Wife;
                                         Last Name Husband & ", " & First Name Husband & " and " & Last Name Wife & ", " & First Name Wife;

                      Not IsEmpty(First Name Husband) AND IsEmpty(First Name Wife) ;
                                               Last Name Husband & ", " & First Name Husband;

                      IsEmpty(First Name Husband) AND Not IsEmpty(First Name Wife) ;
                                                           Last Name Wife & ", " & First Name Wife
                   )