I've a question about a word 2000 mail merge :

a customer of ours is trying to create a document with an IF THEN ELSE
statement.
What he likes to do is the following : he wants to test if a field contains
a certain value.
I know it works fine when you do this :

{ IF {MERGEFIELD M_148} = "verandering*" "{MERGEFIELD M_201}" ""}
or
{ IF {MERGEFIELD M_148} = "*verandering" "{MERGEFIELD M_201}" ""}

So the word "verandering" has to be in the beginning (cfr. verandering*) or
at the end (cfr. *verandering).

But : it's possible that the word "verandering" is somewhere inbetween. And
it seems that trying
{ IF {MERGEFIELD M_148} = "*verandering*" "{MERGEFIELD M_201}" ""}, is not
working.

Do you have any tips ?

Thanks in advance !

Yes, you can't use asterisks at both ends. I do not believe there is a way
to combine multiple IF statements to do this, either. You have to consider
doing this in your data source, e.g. via a Query in Access that uses iif and
instr functions.


Peter Jamieson
"Gert Raes" wrote in message
...
I've a question about a word 2000 mail merge :

a customer of ours is trying to create a document with an IF THEN ELSE
statement.
What he likes to do is the following : he wants to test if a field
contains
a certain value.
I know it works fine when you do this :

{ IF {MERGEFIELD M_148} = "verandering*" "{MERGEFIELD M_201}" ""}
or
{ IF {MERGEFIELD M_148} = "*verandering" "{MERGEFIELD M_201}" ""}

So the word "verandering" has to be in the beginning (cfr. verandering*)
or
at the end (cfr. *verandering).

But : it's possible that the word "verandering" is somewhere inbetween.
And
it seems that trying
{ IF {MERGEFIELD M_148} = "*verandering*" "{MERGEFIELD M_201}" ""}, is not
working.

Do you have any tips ?

Thanks in advance !

Nope, I've tried nested If statements but that doesn't do the job.
For those who want to give it a shot :
- the datasource is a tab delimited textfile (I can't help it, that's how
our programmers decided it)
- the Mergefield M_148 can contain multiple lines. If so, each line will be
separated by a ¶

TIA,
Gert

"Peter Jamieson" schreef:

Yes, you can't use asterisks at both ends. I do not believe there is a way
to combine multiple IF statements to do this, either. You have to consider
doing this in your data source, e.g. via a Query in Access that uses iif and
instr functions.


Peter Jamieson
"Gert Raes" wrote in message
...
I've a question about a word 2000 mail merge :

a customer of ours is trying to create a document with an IF THEN ELSE
statement.
What he likes to do is the following : he wants to test if a field
contains
a certain value.
I know it works fine when you do this :

{ IF {MERGEFIELD M_148} = "verandering*" "{MERGEFIELD M_201}" ""}
or
{ IF {MERGEFIELD M_148} = "*verandering" "{MERGEFIELD M_201}" ""}

So the word "verandering" has to be in the beginning (cfr. verandering*)
or
at the end (cfr. *verandering).

But : it's possible that the word "verandering" is somewhere inbetween.
And
it seems that trying
{ IF {MERGEFIELD M_148} = "*verandering*" "{MERGEFIELD M_201}" ""}, is not
working.

Do you have any tips ?

Thanks in advance !

There are a few things you can consider, and they all suffer from problems.
The three main approaches a
a. preprocess your data in some way to give you the data source you need
b. issue the query you need in Word VBA using OpenDataSource
c. use a DATABASE field to return the item you need.

There are potentially many ways to do (a) - ideally, your programmers would
give you the data you needed but you must judge how difficult that is :-)

Unfortunately (b) and (c) will only work if the connection to the data uses
ODBC, and you have the necessary bits of the Jet (Access) database engine on
the system that is doing the merge. If you do, or it is relatively simple to
set that up, you can try the following:
a. set up an ODBC machine DSN for text files, if there isn't one already
(have a look around Control Panel or Control Panel|Administrative Tools for
the ODBC tool to do this). Unfortunately, in this case, you may also need to
set up an entry for this file to specify that it is tab-delimied (otherwise
in some cases, Word will treat each record as a single field.). I can go
into this if you need, or search this group using google groups for jamieson
odbc schema.ini and you may find something.
b.open your mail merge main document and disconnect from the data source.
Then connect, and check "Select method" in the Open Data Source dialog box.
You should be prompted for the connection method to use - choose the one
with "ODBC" in it.
c. verify that your merge works as it should using ODBC. It may, for
example, treat multiline text differently, and numbers and dates may be
displayed differently. If it doesn't do enough of what you need, abandon
this approach.
d. Now you either need some VBA, or you need a DATABASE field. The former
is preferable, but perhaps a bit less flexible. Also, if you are not
familiar with VBA you could try the latter to see if it works.

The VBA approach:
e. create a VBA sub as follows, run it /once/, and save/close your document
(notice that once Word has done the OpenDataSource, the connection and the
SQL are defined, they are retained, so you do not have to keep this code in
the document or a template except for maintenance purposes). Let us suppose
your ODBC DSN is claled "Text Files", your data file is called "mydata.txt",
and is in a folder called "c:\mydata"

Substitute the various pieces of text marked with the values specific to
your system (and remove the )

Sub MyConnect()

With ActiveDocument.MailMerge

' disconnect the existing data source
.MainDocumentType = wdNotAMergeDocument
' substitute the document type you need
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:="", _
Connection:="DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;", _
SQLStatement:="SELECT * FROM mydata.txt"

End With

End Sub

Run that, and see if the merge document is still working properly. If not,
there is probably a mistake in the code.

When it is OK, change the code to

Sub MyConnect()

With ActiveDocument.MailMerge

' disconnect the existing data source
.MainDocumentType = wdNotAMergeDocument
' substitute the document type you need
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:="", _
Connection:="DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;", _
SQLStatement:="SELECT *, iif(instr(1,M_148,'verandering')0,M_148,'') AS
M_148A FROM mydata.txt"

End With

End Sub

This should create a data source with an additional column called M_148A
that contains M_148 if the text "verandering" is found in it and blank if
not.

If you need to test a lot of fields in this way, you will run out of space
in SQLSattement (it is limited to 255 characters). You can extend the code
to SQLStatement1, but only to around 511 characters. if you have a very
large number of columns in your file, you probably won't be able to connect
using ODBC anyway.

The DATABASE field approach:
For this, you need ato be able to select individual records in your data
source using SQL, i.e. your data needs a unique primary key, which could be
one field or several. Here' I assume you have a field called K containing a
unique sequence number.

Enable the Database toolbar in Word, and click the Insert Database button.

Select your data source as above, selecting the ODBC method, and insert the
data as a field. You should end up with a field that looks something like

{ DATABASE \c "DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;"
\s "SELECT * FROM mydata.txt" \h }

(Actually there will be much more in the \c parameter but you can remove
some parts of it if you want).

Select the field and re-execute it to make sure it works.

Remove the \h and Modify the SELECT so you have

{ DATABASE \c "DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;"
\s "SELECT M_148 FROM mydata.txt WHERE k = { MERGEFIELD k }" }

(If your k is a text field, you will need to put single quotes around {
MERGEFIELD k } ). he {} should be inserted using ctrl-F9 as usual.

Re-execute the field. You should see the value of M_148 for the current
record being previewed.

Change the SELECT again so you have

{ DATABASE \c "DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;"
\s "SELECT iif(instr(1,M_148,'verandering')0,M_148,'') FROM mydata.txt
WHERE k = { MERGEFIELD k }" }

and re-execute the field. You should see the result you need.

Notice that in later Word SPs, Word does not re-evaluate the DATABASE field
automatically when you preview data and move from record to record, unless
you have made the relevant change in the registry, but when you actually
perform the merge you should still see the correct results. However, again,
if you have lots of these tests to make, you will have lots of DATABASE
fields, and I am not sure that Word will cope with the number of connections
required.

Peter Jamieson
"Gert Raes" wrote in message
...
Nope, I've tried nested If statements but that doesn't do the job.
For those who want to give it a shot :
- the datasource is a tab delimited textfile (I can't help it, that's how
our programmers decided it)
- the Mergefield M_148 can contain multiple lines. If so, each line will
be
separated by a ¶

TIA,
Gert

"Peter Jamieson" schreef:

Yes, you can't use asterisks at both ends. I do not believe there is a
way
to combine multiple IF statements to do this, either. You have to
consider
doing this in your data source, e.g. via a Query in Access that uses iif
and
instr functions.


Peter Jamieson
"Gert Raes" wrote in message
...
I've a question about a word 2000 mail merge :

a customer of ours is trying to create a document with an IF THEN ELSE
statement.
What he likes to do is the following : he wants to test if a field
contains
a certain value.
I know it works fine when you do this :

{ IF {MERGEFIELD M_148} = "verandering*" "{MERGEFIELD M_201}" ""}
or
{ IF {MERGEFIELD M_148} = "*verandering" "{MERGEFIELD M_201}" ""}

So the word "verandering" has to be in the beginning (cfr.
verandering*)
or
at the end (cfr. *verandering).

But : it's possible that the word "verandering" is somewhere inbetween.
And
it seems that trying
{ IF {MERGEFIELD M_148} = "*verandering*" "{MERGEFIELD M_201}" ""}, is
not
working.

Do you have any tips ?

Thanks in advance !