EXCEL TO WORD - MERGE FIELDS AND IF STATEMENTS

[Greg]

I have a large Excel spreadsheet with 23 columns and thousands of rows which I use for Word mail merge purposes.

Each row of the spreadsheet contains the names and addresses of motor vehicle owners and motor vehicle drivers, although not always both. When it contains the names and addresses of the drivers, I want my letter addressed to the drivers. When it doesn't contain the names and addresses of the drivers I want the letter addressed to the owners instead.

My attempts to create a suitable IF statement for use in my letters have so far been unsuccessful and yet I cannot see my errors.

Here's an example of what I have tried:

{ IF { MERGEFIELD DriverFirstName} = "" { MERGEFIELD OwnerFirstName } { MERGEFIELD DriverFirstName }}

In other words, if my spreadsheet has no data for the driver's first name, then I want to use the owner's first name. I have tried various permutations of the above statement but clearly it's missing something and I don't what that something is.

I hope someone can tell me how the IF statement should be written to achieve the desired result.

Many thanks in advance.

[macropod]

Your field depiction looks fine. Two questions:
1. How are you creating the field braces (i.e. {})? They should all be created via Ctrl-F9.
2. What does { MERGEFIELD DriverFirstName} output when it's supposed to be empty (e.g. what appears between *{ MERGEFIELD DriverFirstName}*)? If there's anything there, that's what you need to put between the "".

[Greg]

Hi Macropod. I have correctly used Ctrl+F9 to create the curly brackets.

When DriversFirstName is empty it is supposed to output OwnersFirstName in lieu.

I had thought that the double inverted commas (i.e. "") were to be used when a particular cell was empty. Is that my mistake? Should I be using something else for an empty cell?

[westconn1]

it worked correctly for me, on testing

[macropod]

When DriversFirstName is empty it is supposed to output OwnersFirstName in lieu.
I realise that, but you haven't answered the second question.

I had thought that the double inverted commas (i.e. "") were to be used when a particular cell was empty.
True, but let's be sure the merge interprets it as empty and not, say, as a space( or something else), which is the whole point of the second question.

[Greg]

I'm not sure whether the merge interprets the space as anything at all.

I tried to test this by deleting the actual first driver's name from the spreadsheet but it made no difference.

It seems as though the particular cell isn't being read or recognised but I can't give an explanation for that.

[macropod]

How about testing it as I suggested?!! Simply put the mergefield between two asterisks (or any two other printable characters), then run the merge.

BTW: Have you been Finishing the merge, or merely Previewing it? The latter is not a reliable indicator of the final output.

[Greg]

I'm at home now but I have just created a simple spreadsheet to test the merge with (see attached). It behaves the same way as my larger spreadsheet.

[Greg]

It seems to be working now. However, the simple spreadsheet that I just sent may have had an error in it.

[macropod]

... I have just created a simple spreadsheet to test the merge with (see attached). It behaves the same way as my larger spreadsheet.
That is hardly the point. Your attachment has only one record and doesn't test what happens when you do have a DriverFirstName. For the one record you do have, the field code works correctly...

And you still haven't answered the questions I've asked. Until you can do that, I don't propose to waste any more of my time on the matter.

[Greg]

No. I'm stumped. It's not working for me at all.

[Greg]

Paul, I'll try not to waste your time but I've been experimenting and I find that even if I remove the first driver name from the record and recreate the IF statement, the first driver's name is still produced on the Word document. It doesn't automatically default to the first owner's name. Indeed, it doesn't make sense because the record doesn't exist at that point.

[westconn1]

for whatever reason it did not like the empty columns in your data sheet, with those columns removed it then worked, check the specifications for data sources

[macropod]

westconn1: That's hardly a useful approach; one cannot ordinarily modify a data source just for the sake of a mailmerge. In any event, the empty columns are not the issue.

[westconn1]

n any event, the empty columns are not the issue.it does not matter if the columns are empty as long as they have headers, it certainly changes whether the results of the if criteria return as desired

i did suggest

check the specifications for data sourceswhether you can modify the data source or not it must meet the criteria for the mailmerge to work

[macropod]

it does not matter if the columns are empty as long as they have headers
Rubbish!

[westconn1]

Rubbish!well feel free to fix the problem, though you had already stated you were not interested in doing so

[macropod]

well feel free to fix the problem, though you had already stated you were not interested in doing so
Literacy's evidently not your strong point either. Try reading what I posted...

[SamT]

Hay, old roo, I figure since I gotta be making all the MailMerge templates for the department, I better leran what it takes as quick as possible, so 'm browsing the Word forum.

I looked at Greg's attachments. In the docx he has
{ IF { MERGEFIELD DriverFirstName} = "" { MERGEFIELD OwnerFirstName } { MERGEFIELD DriverFirstName }}So... are all those spaces before and after some of the curly braces allowed?



@Greg,
I updated your xlsx with some more rows. Run your mailmerge on it and tell us (ok, I mean, tell Macropod,) what happens to all four merges.

[macropod]

The 'curly braces' are field braces inserted via Ctrl-F9, or what you see if you insert a mergefield from the Inset Mergefield dropdown (which would create a field that looks like «DriverFirstName», for example), then select the field and press Shift-F9. You can't type or copy/paste them from this forum.

As for the spaces within each pair of field braces, they're typically put there by Word. For the field in your post, the minimum amount of spaces you could have and get the field to work is with:
{IF{MERGEFIELD DriverFirstName}= "" {MERGEFIELD OwnerFirstName} {MERGEFIELD DriverFirstName}}

[SamT]

Thanks.

Just to check my understanding

IFCondition (space) Value (space) TrueAction (space) FalseAction

[macropod]

Just to check my understanding

IFCondition (space) Value (space) TrueAction (space) FalseAction
Purists would say:
IF (space) Variable (space) Condition (space) Value (space) TrueAction (space) FalseAction
In 'IF{MERGEFIELD DriverFirstName}=' the field braces substitute for the otherwise-required spaces.

[SamT]

Thanks, Paul.

The daughter of WordBasic is a whole other critter than Excel.

[macropod]

Word field coding has nothing to do with WordBasic or VBA for Word. In fact, it pre-dates both.