I am merging some letters, and I have a merge field that lists a number of
codes separate by new lines. I need to include another document if a
particular code is in the mergefield. I know that you can use IF MergeField =
"string", but that returns false. I have tried IF MergeField = "*string*",
but that also returns false. Is there another way to test if a merge field
contains a string? Thanks. I can provide more information if needed.
--
- Joshua Pangborn

You are not going to be able to do that with an IF field during the mail
merge process. What you will have to do is modify the data source so that
the string is in a separate field. Just how you go about doing that will
depend upon the data source. It will probably require a macro to do it, but
it is not too difficult.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Joshua Pangborn" wrote in
message ...
I am merging some letters, and I have a merge field that lists a number of
codes separate by new lines. I need to include another document if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField = "*string*",
but that also returns false. Is there another way to test if a merge field
contains a string? Thanks. I can provide more information if needed.
--
- Joshua Pangborn

What is the data source (Access, SQL Server,...)?

Peter Jamieson
"Joshua Pangborn" wrote in
message ...
I am merging some letters, and I have a merge field that lists a number of
codes separate by new lines. I need to include another document if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField = "*string*",
but that also returns false. Is there another way to test if a merge field
contains a string? Thanks. I can provide more information if needed.
--
- Joshua Pangborn

The answer to that has two parts. The initial datasource is a Oracle
Database. I run a process that extracts the data I need and creates a Comma
Delimited Text file which I use in the merge. I have no control over how the
Oracle Database generates the text file.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

What is the data source (Access, SQL Server,...)?

Peter Jamieson
"Joshua Pangborn" wrote in
message ...
I am merging some letters, and I have a merge field that lists a number of
codes separate by new lines. I need to include another document if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField = "*string*",
but that also returns false. Is there another way to test if a merge field
contains a string? Thanks. I can provide more information if needed.
--
- Joshua Pangborn

It's going to be a lot easier to achieve what you are after if the data is
converted into a table, which can be done by opening the csv file in Word
and then selecting everything and use the Convert Text to Table item on the
Tables toolbar. Then can use a macro to iterate through the cells in the
column that may or may not contain the string and use the InStr() function
to determine if it is present. What I would do is have the macro add
another column to the table and populate that with the result of the InStr()
function which will be 0 if the string does not exist or something greater
than 0 if it does. Then, if you use that table as the data source for the
merge, you can have the If...then...Else field check on the values that are
in that new column and include the other document if appropriate.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Joshua Pangborn" wrote in
message ...
The answer to that has two parts. The initial datasource is a Oracle
Database. I run a process that extracts the data I need and creates a
Comma
Delimited Text file which I use in the merge. I have no control over how
the
Oracle Database generates the text file.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

What is the data source (Access, SQL Server,...)?

Peter Jamieson
"Joshua Pangborn" wrote in
message ...
I am merging some letters, and I have a merge field that lists a number
of
codes separate by new lines. I need to include another document if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField =
"*string*",
but that also returns false. Is there another way to test if a merge
field
contains a string? Thanks. I can provide more information if needed.
--
- Joshua Pangborn

If this is a one-off, and you have either Excel or Access, I would probably
either
a. import the data into Access and create a query that uses instr to detect
the string (cf. Doug's approach), then use that as the data source for the
merge or
b. import the data into Excel and create a new column that uses INSTR (I
think - I'm not so familiar with Excel) to do the same.

If you're doing this regulaly on one machine, you might be able to adapt
approach (a) by linking to the file instead of importing it.

However, what I would probably try first is as follows. There is a lot to
it, and if you are starting with no knowledge of VBA and ODBC it may not be
worth pursuing, but once working, it's a low-maintenance approach, at least
if used on a single machine.

a. apply the registry patch described in

http://support.microsoft.com/default...b;en-us;825765

b. run a macro based on the following code. If you haven't used Word VBA
macros before, you may find the following article, and others on the same
site, useful:

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Sub OpenDataSourceViaODBC

Dim strConnection As String
Dim strQuery As String
Dim strTextFileFolder As String
Dim strTextFileName As String

' Add your own error checking as required

' Set the following string to the pathname of the folder
' containing the data source

strTextFileFolder = "c:\My Data Sources\"

' Set the following string to the file name

strTextFileName = "myfile.txt"

' Construct an ODBC connection string
' (you may not need the last line)

strConnection = _
"DSN=Delimited Text Files;DBQ=" & _
strTextFileFolder & ";" & _
"DriverId=27;FIL=text;"

' Construct a Query string. You need to adapt this to
' do what you need. Let's suppose you are looking for
' the code "abc" in a column named "mycolumn"
' The following SQL should return code as 0 if the string is not found

' Notice that there are three types of quotes in he
' double quotes " to enclose the string
' single quotes ' to enclose strings passed to SQL
' single backquotes ` to surround the alias name "code"
' and the file name (you only need them if the file name contains
' characters such as spaces, and if you leave out "AS `code`"
' SQL will invent a column name for you anyway

strQuery = _
"SELECT instr(1,mycolumn,'abc') AS `code`, * FROM `" & _
strTextFileName & _
"`"

' Ensure any existing data source is closed

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

' set the merge type to the one you need. Here, it's for Form Letters

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

' Open the data source

ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection := strConnection, _
SQLStatement := strQuery, _
SubType := wdMergeSubTypeWord2000

End Sub

You should only need to do this as a one-off (assuming you don't change the
file name, location or query) so you can run it to make the connection, then
remove the macro from the template/document if you wish.

However, there are a number of gotchas and things to know about this code:
a. It relies on the existence of an ODBC User or System DSN called
"Delimited Text Files". Since you're using Word 2003, I think there will be
such a DSN on your system but it's not guaranteed. You can create it if
necessary using the ODBC Administrator (find the Administrative tools in
Control Panel)
b. it will probably only work with files that have certain extensions (txt,
csv, possibly one or two others). Further, the end-of-record delimiter must
be CRLF.
c. it /may/ also rely on the existence of an ODBC info. file called
schema.ini that would be in the same folder as your text file. schema.ini
contains per-file information about the delimiters and column headers. You
can maintain the file in the ODBC Administrator or (slightly more
dangerously) in Notepad. For example, a suitable SCHEMA.INI for the above
file would contain the following. As tested here, Word seemed to be able to
read my test file without a Schema.ini.

[myfile.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM

If you need to extract lots of different codes, there is a problem because
you have to define them all in advance using the instr approach. You may run
out of space in the Query (you get about 255 characters, and you may be able
to get another 255 by providing an additional string parameter,
SQLStatement1, to OpenDataSource.

If, however, you have certain codes at certain locations in your text, you
could extract them using left, mid, right functions and create separate
fields.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
The answer to that has two parts. The initial datasource is a Oracle
Database. I run a process that extracts the data I need and creates a
Comma
Delimited Text file which I use in the merge. I have no control over how
the
Oracle Database generates the text file.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

What is the data source (Access, SQL Server,...)?

Peter Jamieson
"Joshua Pangborn" wrote in
message ...
I am merging some letters, and I have a merge field that lists a number
of
codes separate by new lines. I need to include another document if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField =
"*string*",
but that also returns false. Is there another way to test if a merge
field
contains a string? Thanks. I can provide more information if needed.
--
- Joshua Pangborn

Doug and Peter,

Thanks for the replies. The Convert Text to Table method is not something
that I would like to do. Once I have this working, it will be run regularly
by a person who is not as knowledgable about Word, so I need to make it as
simple as possible for them. The ODBC Datasource option sounds interesting. I
am going to look into that some more. I just want to bounce an idea off
everyone. I was looking at the VBA for Word reference on MSDN, and saw that I
could use the MailMergeDataField object to get the value of a particular
field. Once I assign the value of the field to a variable, I could easily
search the string for the proper substrings. My question with this idea is
how to make sure this process happens for each record as I merge the letters
to a new document. Does this sound like an option, or am I not thinking
clearly? Thanks.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

If this is a one-off, and you have either Excel or Access, I would probably
either
a. import the data into Access and create a query that uses instr to detect
the string (cf. Doug's approach), then use that as the data source for the
merge or
b. import the data into Excel and create a new column that uses INSTR (I
think - I'm not so familiar with Excel) to do the same.

If you're doing this regulaly on one machine, you might be able to adapt
approach (a) by linking to the file instead of importing it.

However, what I would probably try first is as follows. There is a lot to
it, and if you are starting with no knowledge of VBA and ODBC it may not be
worth pursuing, but once working, it's a low-maintenance approach, at least
if used on a single machine.

a. apply the registry patch described in

http://support.microsoft.com/default...b;en-us;825765

b. run a macro based on the following code. If you haven't used Word VBA
macros before, you may find the following article, and others on the same
site, useful:

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Sub OpenDataSourceViaODBC

Dim strConnection As String
Dim strQuery As String
Dim strTextFileFolder As String
Dim strTextFileName As String

' Add your own error checking as required

' Set the following string to the pathname of the folder
' containing the data source

strTextFileFolder = "c:\My Data Sources\"

' Set the following string to the file name

strTextFileName = "myfile.txt"

' Construct an ODBC connection string
' (you may not need the last line)

strConnection = _
"DSN=Delimited Text Files;DBQ=" & _
strTextFileFolder & ";" & _
"DriverId=27;FIL=text;"

' Construct a Query string. You need to adapt this to
' do what you need. Let's suppose you are looking for
' the code "abc" in a column named "mycolumn"
' The following SQL should return code as 0 if the string is not found

' Notice that there are three types of quotes in he
' double quotes " to enclose the string
' single quotes ' to enclose strings passed to SQL
' single backquotes ` to surround the alias name "code"
' and the file name (you only need them if the file name contains
' characters such as spaces, and if you leave out "AS `code`"
' SQL will invent a column name for you anyway

strQuery = _
"SELECT instr(1,mycolumn,'abc') AS `code`, * FROM `" & _
strTextFileName & _
"`"

' Ensure any existing data source is closed

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

' set the merge type to the one you need. Here, it's for Form Letters

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

' Open the data source

ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection := strConnection, _
SQLStatement := strQuery, _
SubType := wdMergeSubTypeWord2000

End Sub

You should only need to do this as a one-off (assuming you don't change the
file name, location or query) so you can run it to make the connection, then
remove the macro from the template/document if you wish.

However, there are a number of gotchas and things to know about this code:
a. It relies on the existence of an ODBC User or System DSN called
"Delimited Text Files". Since you're using Word 2003, I think there will be
such a DSN on your system but it's not guaranteed. You can create it if
necessary using the ODBC Administrator (find the Administrative tools in
Control Panel)
b. it will probably only work with files that have certain extensions (txt,
csv, possibly one or two others). Further, the end-of-record delimiter must
be CRLF.
c. it /may/ also rely on the existence of an ODBC info. file called
schema.ini that would be in the same folder as your text file. schema.ini
contains per-file information about the delimiters and column headers. You
can maintain the file in the ODBC Administrator or (slightly more
dangerously) in Notepad. For example, a suitable SCHEMA.INI for the above
file would contain the following. As tested here, Word seemed to be able to
read my test file without a Schema.ini.

[myfile.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM

If you need to extract lots of different codes, there is a problem because
you have to define them all in advance using the instr approach. You may run
out of space in the Query (you get about 255 characters, and you may be able
to get another 255 by providing an additional string parameter,
SQLStatement1, to OpenDataSource.

If, however, you have certain codes at certain locations in your text, you
could extract them using left, mid, right functions and create separate
fields.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
The answer to that has two parts. The initial datasource is a Oracle
Database. I run a process that extracts the data I need and creates a
Comma
Delimited Text file which I use in the merge. I have no control over how
the
Oracle Database generates the text file.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

What is the data source (Access, SQL Server,...)?

Peter Jamieson
"Joshua Pangborn" wrote in
message ...
I am merging some letters, and I have a merge field that lists a number
of
codes separate by new lines. I need to include another document if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField =
"*string*",
but that also returns false. Is there another way to test if a merge
field
contains a string? Thanks. I can provide more information if needed.
--
- Joshua Pangborn

Yes, you can use that approach but to get the value for each record you have
to use Word's MailMerge Events, specifically the BeforeRecordMerge event.
You can't actually create merge fields or modify their data, but you can,
for example, use VBA to test the data then either insert the file you want
directly, or insert a field that Merge will execute, etc. etc.

The Word VBA Help gives an example of how to use Events if you have not done
that before. Don't expect the event that fires before the merge starts to
"fire" unless you initiate the merge from the Mail Merge Task Pane, BTW.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
Doug and Peter,

Thanks for the replies. The Convert Text to Table method is not something
that I would like to do. Once I have this working, it will be run
regularly
by a person who is not as knowledgable about Word, so I need to make it as
simple as possible for them. The ODBC Datasource option sounds
interesting. I
am going to look into that some more. I just want to bounce an idea off
everyone. I was looking at the VBA for Word reference on MSDN, and saw
that I
could use the MailMergeDataField object to get the value of a particular
field. Once I assign the value of the field to a variable, I could easily
search the string for the proper substrings. My question with this idea is
how to make sure this process happens for each record as I merge the
letters
to a new document. Does this sound like an option, or am I not thinking
clearly? Thanks.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

If this is a one-off, and you have either Excel or Access, I would
probably
either
a. import the data into Access and create a query that uses instr to
detect
the string (cf. Doug's approach), then use that as the data source for
the
merge or
b. import the data into Excel and create a new column that uses INSTR (I
think - I'm not so familiar with Excel) to do the same.

If you're doing this regulaly on one machine, you might be able to adapt
approach (a) by linking to the file instead of importing it.

However, what I would probably try first is as follows. There is a lot to
it, and if you are starting with no knowledge of VBA and ODBC it may not
be
worth pursuing, but once working, it's a low-maintenance approach, at
least
if used on a single machine.

a. apply the registry patch described in

http://support.microsoft.com/default...b;en-us;825765

b. run a macro based on the following code. If you haven't used Word VBA
macros before, you may find the following article, and others on the same
site, useful:

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Sub OpenDataSourceViaODBC

Dim strConnection As String
Dim strQuery As String
Dim strTextFileFolder As String
Dim strTextFileName As String

' Add your own error checking as required

' Set the following string to the pathname of the folder
' containing the data source

strTextFileFolder = "c:\My Data Sources\"

' Set the following string to the file name

strTextFileName = "myfile.txt"

' Construct an ODBC connection string
' (you may not need the last line)

strConnection = _
"DSN=Delimited Text Files;DBQ=" & _
strTextFileFolder & ";" & _
"DriverId=27;FIL=text;"

' Construct a Query string. You need to adapt this to
' do what you need. Let's suppose you are looking for
' the code "abc" in a column named "mycolumn"
' The following SQL should return code as 0 if the string is not found

' Notice that there are three types of quotes in he
' double quotes " to enclose the string
' single quotes ' to enclose strings passed to SQL
' single backquotes ` to surround the alias name "code"
' and the file name (you only need them if the file name contains
' characters such as spaces, and if you leave out "AS `code`"
' SQL will invent a column name for you anyway

strQuery = _
"SELECT instr(1,mycolumn,'abc') AS `code`, * FROM `" & _
strTextFileName & _
"`"

' Ensure any existing data source is closed

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

' set the merge type to the one you need. Here, it's for Form Letters

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

' Open the data source

ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection := strConnection, _
SQLStatement := strQuery, _
SubType := wdMergeSubTypeWord2000

End Sub

You should only need to do this as a one-off (assuming you don't change
the
file name, location or query) so you can run it to make the connection,
then
remove the macro from the template/document if you wish.

However, there are a number of gotchas and things to know about this
code:
a. It relies on the existence of an ODBC User or System DSN called
"Delimited Text Files". Since you're using Word 2003, I think there will
be
such a DSN on your system but it's not guaranteed. You can create it if
necessary using the ODBC Administrator (find the Administrative tools in
Control Panel)
b. it will probably only work with files that have certain extensions
(txt,
csv, possibly one or two others). Further, the end-of-record delimiter
must
be CRLF.
c. it /may/ also rely on the existence of an ODBC info. file called
schema.ini that would be in the same folder as your text file.
schema.ini
contains per-file information about the delimiters and column headers.
You
can maintain the file in the ODBC Administrator or (slightly more
dangerously) in Notepad. For example, a suitable SCHEMA.INI for the above
file would contain the following. As tested here, Word seemed to be able
to
read my test file without a Schema.ini.

[myfile.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM

If you need to extract lots of different codes, there is a problem
because
you have to define them all in advance using the instr approach. You may
run
out of space in the Query (you get about 255 characters, and you may be
able
to get another 255 by providing an additional string parameter,
SQLStatement1, to OpenDataSource.

If, however, you have certain codes at certain locations in your text,
you
could extract them using left, mid, right functions and create separate
fields.

Peter Jamieson

"Joshua Pangborn" wrote in
message ...
The answer to that has two parts. The initial datasource is a Oracle
Database. I run a process that extracts the data I need and creates a
Comma
Delimited Text file which I use in the merge. I have no control over
how
the
Oracle Database generates the text file.
--
- Joshua Pangborn


"Peter Jamieson" wrote:

What is the data source (Access, SQL Server,...)?

Peter Jamieson
"Joshua Pangborn" wrote in
message ...
I am merging some letters, and I have a merge field that lists a
number
of
codes separate by new lines. I need to include another document if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField =
"*string*",
but that also returns false. Is there another way to test if a merge
field
contains a string? Thanks. I can provide more information if needed.
--
- Joshua Pangborn

Hi Joshua,

This is easy enough to do with wildcards in the field coding if the
substring always starts at the same position in the overall string. For
example, if the substring to look for always begins at the 5th character:

{IF{MergeField MyData}= ????TestString* "True Result" False Result"}

For anything involving variable string positioning, a field-code solution
could be horribly complex.

Cheers


"Joshua Pangborn" wrote in
message ...
I am merging some letters, and I have a merge field that lists a number of
codes separate by new lines. I need to include another document if a
particular code is in the mergefield. I know that you can use IF
MergeField =
"string", but that returns false. I have tried IF MergeField = "*string*",
but that also returns false. Is there another way to test if a merge field
contains a string? Thanks. I can provide more information if needed.
--
- Joshua Pangborn

[WordBanter AI]

Hi Joshua,

Yes, you can use the IF statement to find whether a Merge Field contains a substring. Here's how you can do it:

1. Insert the Merge Field into your document by going to the Mailings tab, clicking on Insert Merge Field, and selecting the field you want to use.
2. In the IF statement, use the following syntax:
   PHP Code:

   IF {MERGEFIELD fieldname} = "" then "false" else "true" 

3. Replace "fieldname" with the name of your Merge Field.
4. Replace "" with the substring you want to search for.
5. If the substring is found in the Merge Field, the IF statement will return "true". If it is not found, the IF statement will return "false".

For example, if your Merge Field is called "Codes" and you want to search for the substring "ABC", your IF statement would look like this:

PHP Code:

IF {MERGEFIELD Codes} = "*ABC*" then "true" else "false"