Topic Options
#39340 - 04/23/12 04:10 PM His and Her LastName - How do I do it?
TimM Offline
OL Newbie

Registered: 04/23/12
Posts: 2
I looked and found lots of things really close to what I want to do - but nothing that is quite "it".

I have an Excel file. In that file are "First" "Spouse" and "Proper" fields...

"First" is his or her first name.
"Spouse" is his or her husband or wife's first name.
"Proper" is their last name.

THE GOAL is to print Bob and Betty Smith - or just Bob Smith if the "Spouse" field is blank.

My PROBLEM is that I need to be sure the 'and' does not print if "Spouse" is blank...

- I already discovered how to make their names into Mixed Case where they are all caps in the excel file. Did that with PROPER(field) and that may be adding to my confusion when I try to use the expression editor.

Top
#39343 - 04/24/12 09:32 AM Re: His and Her LastName - How do I do it? [Re: TimM]
Raphael Lalonde Lefebvre Offline
OL Expert

Registered: 10/14/05
Posts: 4953
Loc: Objectif Lune Montreal
Tim,

You can do this by placing only a single variable on the line where you want the name, without any static spaces or a static "and". Then, in this variable, you will use an expression like this one:

IF([Spouse] <> "", [First] + " and " + [Spouse] + " " + [Proper], [First] + " " + [Proper])

This should do the trick. It will add the "and" and display the [Spouse] field if it's not empty. Otherwise, it will only display [First] and [Proper].

If you need to use the PROPER function to the fields, you just have to apply it to the fields. Example:

IF([Spouse] <> "", PROPER([First]) + " and " + PROPER([Spouse]) + " " + PROPER([Proper]), PROPER([First]) + " " + PROPER([Proper]))

This will apply it to all fields.

Hope that helps!

Regards,
Raphaël Lalonde Lefebvre

Top
#39361 - 04/24/12 03:16 PM Re: His and Her LastName - How do I do it? [Re: Raphael Lalonde Lefebvre]
TimM Offline
OL Newbie

Registered: 04/23/12
Posts: 2
Hi Raphaël,

Well, maybe I am not as smart as hoped to be... I tried using the code bits you gave me (Thanks!!) and kept getting "Number Expected" at the " and " part of the code.

In my attempt to debug that error, when I removed that, the same error just moved to a new place. My suspicions are that it is in the way I tried to used what you were kind enough to give me.

I tried just pasting (either) line of code in place of the variables "First" "Spouse" etc.

That showed me a pretty line of code, so I guessed maybe I needed to create a variable. Tried all kinds of goofy things even added a column called variable in my Excel file - so I could have the data field show, then attempt to edit under "Variables"

I guess I need a little more help if you can point me in the right direction please.

Top