Nested IIf statement (Word03)

Topic

New Reply

I have a database drive mail merge document that is also supported with side Word documents.

One field in the database is DeptFile, this holds the name of the separate Word document for that unit. The separate Word file holds unit specific information. For example ICU has additional information to be added that no one else needs or there are lists of abbreviations that only apply to one unit. I can format like I need to in Word easier than in the database so I put it in the Word file.

With the abbreviation list some units have them some do not. What I want to do is to avoid the error message ( Error! Filename not specified.) I get if there isn’t a DeptFile or no abbreviation bookmark. The following works if there is material in a bookmark call abbreviations and there is a separate DeptFile.

{INCLUDETEXT “{MERGEFIELD “DeptFile” }” abbreviations }

I am working with the following, but not getting there

{ IIF(Nz({MERGEFIELD “DeptFile” } abbreviations)), {INCLUDETEXT “{MERGEFIELD “DeptFile” }” abbreviations }, “” }

Thank you for any help. I am not sure that Nz is allowed in Word. Fay

Reply | Quote

Replies

IIf is a VBA function. In a field, you must use IF.
Nz is aclusively Access, it is not available in other VBA dialects, and certainly not in fields in Word.
You could try

{ IF { MERGEFIELD DeptFile } = "" "" "{ INCLUDETEXT "{ MERGEFIELD DeptFile }" Abbreviations }" }

Remember, never type the field brackets { } yourself, instead press Ctrl+F9, use Insert | Field or click the Insert Word Field button on the Mail Merge toolbar and select If … Then … Else …

Reply | Quote

Hi Fay,

As Hans said, you need to use the correct field coding syntax.

However, Hans’ solution only works in the ‘DeptFile’ field and the ‘abbreviations’ field are both empty – it doesn’t work if you have a valid filename but the ‘abbreviations’ field is empty or if you have an invalid or missing filename but the ‘abbreviations’ field is populated. Here’s a field code solution that should work for any such scenarios:
{IF{INCLUDETEXT “{MERGEFIELD DeptFile}” Abbreviations}= Error!* “” {INCLUDETEXT “{MERGEFIELD DeptFile}” Abbreviations}}

The above code works via a test string ‘Error!’ followed by a wildcard ‘*’. This works because the error messages you get when the:
. fileneme isn’t found (Error! Filename not specified.)
. fileneme is invalid (Error! Cannot open file.)
. bookmark is given without a filename (Error! Cannot open file.); and
. bookmark isn’t found (Error! Bookmark not defined.)
all begin with ‘Error’ . I am assuming you don’t actually have a ‘DeptFile’ field or an ‘abbreviations’ field starting with ‘Error!’.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi macropod. Thank you for the information. This code gets me so I don’t get an error message but I am not getting the abbreviations brought into the document. I have double check my code against yours, yes {} entered using F9. Copied the bookmark spelling, made sure there was the abbreviation bookmark in at least one of the documents I was trying to access. Any other ideas?

Fay

Reply | Quote

Hi Fay,

I forgot to provide for the abbreviations to be picked up via the merge. The following should fix it!
{IF{INCLUDETEXT “{MERGEFIELD DeptFile}” {MERGEFIELD Abbreviations}}= Error!* “” {INCLUDETEXT “{MERGEFIELD DeptFile}” {MERGEFIELD Abbreviations}}}

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi thank you for working with me on this. That didn’t work either. Remember abbreviations is a bookmark, does that make a difference.

Also would this be easier if it was done in VBA not fields? I haven’t done VBA in word just Access, but what the heck nothing ventured nothing learned.

Fay

Reply | Quote

Hi Fay,

If ‘Abbreviations’ is the name given to the bookmark in the file being included, then
{IF{INCLUDETEXT “{MERGEFIELD DeptFile}” Abbreviations}= Error!* “” {INCLUDETEXT “{MERGEFIELD DeptFile}” Abbreviations}}
should work. If some of your documents lack this bookmark, you’ll need the field to be coded like:
{IF{INCLUDETEXT “{MERGEFIELD DeptFile}” Abbreviations} Error!* {INCLUDETEXT “{MERGEFIELD DeptFile}” Abbreviations} {IF{INCLUDETEXT “{MERGEFIELD DeptFile}”} Error!* {INCLUDETEXT “{MERGEFIELD DeptFile}”}}}

Conversely, if ‘Abbreviations’ is the name of the field in your source document that defines the bookmark, then
{IF{INCLUDETEXT “{MERGEFIELD DeptFile}” {MERGEFIELD Abbreviations}}= Error!* “” {INCLUDETEXT “{MERGEFIELD DeptFile}” {MERGEFIELD Abbreviations}}}
should work. If {MERGEFIELD Abbreviations} gives you a ‘0’ when the ‘Abbreviations’ field in your source document is empty, both instances of {MERGEFIELD Abbreviations} have to be recoded as:
{IF{MERGEFIELD Abbreviations} 0 {MERGEFIELD Abbreviations}}

Note that you must have the double quotes and spaces indicated above for the fields to work reliably.

Cheers
PS: It might also be advisable to add the ! switch to the INCLUDETEXT fields (eg {INCLUDETEXT “{MERGEFIELD DeptFile}” Abbreviations !}). This stops any imported references from changing.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote