[Pip''''n]

I have a mail merge that needs to display various lines of text based on a
field that contains a list of comma delmited product IDs. Each line
corresponds to a particular product.

Is there a way to split the field to the various IDs and loop through the
list to show the lines of text that I need?

For Example:

ProductID 1 = Product 1
ProductID 2 = Product 2
ProductID 3 = Product 3

MERGEFIELD ProductLookup contains string "1, 2"

Text in Word Document:

You have purchased the following products:
Product 1 : Description of Product
Product 2 : Description of Product
Product 3 : Description of Product

Thanks in Advance,

Pip'n

[Peter Jamieson]

In this case I think you will need to modify your data source (i.e. create
the data source you really need), and you may find that when you do so, it
will make sense to use a report generator such as the one in Access to
produce your output rather than Word. If you still need or want to use Word,
you need to think about the overall merge (e.g. is it a "one-to-many" merge,
which Word is not really designed to do?)

What is the data source at the moment? Which version of Word?

Peter Jamieson
"Pip''''n" wrote in message
...
I have a mail merge that needs to display various lines of text based on a
field that contains a list of comma delmited product IDs. Each line
corresponds to a particular product.

Is there a way to split the field to the various IDs and loop through the
list to show the lines of text that I need?

For Example:

ProductID 1 = Product 1
ProductID 2 = Product 2
ProductID 3 = Product 3

MERGEFIELD ProductLookup contains string "1, 2"

Text in Word Document:

You have purchased the following products:
Product 1 : Description of Product
Product 2 : Description of Product
Product 3 : Description of Product

Thanks in Advance,

Pip'n

[Pip''''n]

Currently the Data Source is Microsoft SQL Server 2000 and we're using Word
2007.

The query that i'm using is built dynamically to single out a single record
by a Deal ID. The database is set up in a way that a Deal can have multiple
products, but those products are stored as a comma delimted string in a
single field. (Dont ask me why they did this)

The field that contanis the string is dbo.tblDeals.ProductLookup

The document is called from a Classic ASP page that generates the database
view which is called by the word document. In the page they choose the
account and deal that they want to create the contract for.

Here is the SQL:
SELECT dbo.tblDeals.DealID, dbo.tblAccounts.Type,
dbo.tblAccounts.AccountName, dbo.tblAccounts.Address,
dbo.tblAccounts.Address2, dbo.tblAccounts.City,
dbo.tblAccounts.Province, dbo.tblAccounts.PostalCode,
dbo.tblAccounts.Country, dbo.tblProvinces.Name AS ProvinceName,
dbo.tblDeals.DecisionMakerContact, DATEPART(dd, { fn
NOW() }) AS SignupDay, { fn MONTHNAME({ fn NOW() }) } AS SignupMonth,
DATEPART(yyyy,
{ fn NOW() }) AS SignupYear,
dbo.tblContacts.ContactName, dbo.tblContacts.ContactTitle,
dbo.tblContacts.ContactFirstName,
dbo.tblContacts.ContactSuffix,
dbo.tblDeals.ProductLookup FROM dbo.tblDeals INNER JOIN
dbo.tblAccounts ON dbo.tblDeals.AccountLookup =
dbo.tblAccounts.AccountID INNER JOIN
dbo.tblProvinces ON dbo.tblAccounts.Province =
dbo.tblProvinces.Abbreviation LEFT OUTER JOIN
dbo.tblContacts ON dbo.tblDeals.DecisionMakerContact =
dbo.tblContacts.ContactID
WHERE (dbo.tblDeals.DealID = 1827)

"Peter Jamieson" wrote:

In this case I think you will need to modify your data source (i.e. create
the data source you really need), and you may find that when you do so, it
will make sense to use a report generator such as the one in Access to
produce your output rather than Word. If you still need or want to use Word,
you need to think about the overall merge (e.g. is it a "one-to-many" merge,
which Word is not really designed to do?)

What is the data source at the moment? Which version of Word?

Peter Jamieson
"Pip''''n" wrote in message
...
I have a mail merge that needs to display various lines of text based on a
field that contains a list of comma delmited product IDs. Each line
corresponds to a particular product.

Is there a way to split the field to the various IDs and loop through the
list to show the lines of text that I need?

For Example:

ProductID 1 = Product 1
ProductID 2 = Product 2
ProductID 3 = Product 3

MERGEFIELD ProductLookup contains string "1, 2"

Text in Word Document:

You have purchased the following products:
Product 1 : Description of Product
Product 2 : Description of Product
Product 3 : Description of Product

Thanks in Advance,

Pip'n

[Peter Jamieson]

Something I forgot to ask befo even if you were able to split the
individual ProductIDs out from the ProductLookup field, how would you get
the corresponding Product descriptions during the merge? If the list of
product IDs is small and stable, you could perhaps do it using IF fields and
testing for each ProductID in turn. Otherwise, there's a problem, because
a. the number of Products varies (is there a known limit?)
b. you might be able to do the lookup using a DATABASE field, but these
days, DATABASE fields are more badly behaved than they used to be because
they insert an extra carriage return after the result, even when the result
is a single field

What I would do depends on what technologies I could make use of.
For example, if in this case you are in a position to write a Transact-SQL
procedure/function, I am fairly sure it would be possible to write one that
would take the ProductLookup field as input, and output either a multiline
text field containing "ProductID: Product Description" for each product, or
(not so sure with SQL Server 2000) return a table containing one row with
Product ID and Descriptions. What I'm not at all sure about is whether you
could use the results of those procedures/functions easily in a merge.
Or if you can use Word VBA, then you could for example
c. use Word MailMerge Events to inspect ProductLookup, do the necessary
lookups using ADO, and insert the results into the mail merge main document
pre-merge
d. avoid Word MailMerge altogether and just use ADO to get all the data you
need and build your document using that.

Depending on feasibility etfc. I would have a look at the Transact-SQL
approach first, but in the end there's a lot to be said for heading straight
for (d).

If all that stuff is beyond your scope, I don't know what else to suggest...

Peter Jamieson
"Pip''''n" wrote in message
...
Currently the Data Source is Microsoft SQL Server 2000 and we're using
Word
2007.

The query that i'm using is built dynamically to single out a single
record
by a Deal ID. The database is set up in a way that a Deal can have
multiple
products, but those products are stored as a comma delimted string in a
single field. (Dont ask me why they did this)

The field that contanis the string is dbo.tblDeals.ProductLookup

The document is called from a Classic ASP page that generates the database
view which is called by the word document. In the page they choose the
account and deal that they want to create the contract for.

Here is the SQL:
SELECT dbo.tblDeals.DealID, dbo.tblAccounts.Type,
dbo.tblAccounts.AccountName, dbo.tblAccounts.Address,
dbo.tblAccounts.Address2, dbo.tblAccounts.City,
dbo.tblAccounts.Province, dbo.tblAccounts.PostalCode,
dbo.tblAccounts.Country, dbo.tblProvinces.Name AS ProvinceName,
dbo.tblDeals.DecisionMakerContact, DATEPART(dd, { fn
NOW() }) AS SignupDay, { fn MONTHNAME({ fn NOW() }) } AS SignupMonth,
DATEPART(yyyy,
{ fn NOW() }) AS SignupYear,
dbo.tblContacts.ContactName, dbo.tblContacts.ContactTitle,
dbo.tblContacts.ContactFirstName,
dbo.tblContacts.ContactSuffix,
dbo.tblDeals.ProductLookup FROM dbo.tblDeals INNER JOIN
dbo.tblAccounts ON dbo.tblDeals.AccountLookup =
dbo.tblAccounts.AccountID INNER JOIN
dbo.tblProvinces ON dbo.tblAccounts.Province =
dbo.tblProvinces.Abbreviation LEFT OUTER JOIN
dbo.tblContacts ON dbo.tblDeals.DecisionMakerContact
=
dbo.tblContacts.ContactID
WHERE (dbo.tblDeals.DealID = 1827)

"Peter Jamieson" wrote:

In this case I think you will need to modify your data source (i.e.
create
the data source you really need), and you may find that when you do so,
it
will make sense to use a report generator such as the one in Access to
produce your output rather than Word. If you still need or want to use
Word,
you need to think about the overall merge (e.g. is it a "one-to-many"
merge,
which Word is not really designed to do?)

What is the data source at the moment? Which version of Word?

Peter Jamieson
"Pip''''n" wrote in message
...
I have a mail merge that needs to display various lines of text based on
a
field that contains a list of comma delmited product IDs. Each line
corresponds to a particular product.

Is there a way to split the field to the various IDs and loop through
the
list to show the lines of text that I need?

For Example:

ProductID 1 = Product 1
ProductID 2 = Product 2
ProductID 3 = Product 3

MERGEFIELD ProductLookup contains string "1, 2"

Text in Word Document:

You have purchased the following products:
Product 1 : Description of Product
Product 2 : Description of Product
Product 3 : Description of Product

Thanks in Advance,

Pip'n

[Pip''''n]

Currently the Descriptions are written for this specific document. They were
Hard Coded into the Contract document and the Salesmen would just remove the
ones that they didn't need. This happens in about 4 different locations in
the document.

There are 22 products which correspond to 13 different descriptions (some
products use the same description)

Right now I have it set up with If statements which would work if there was
only one product chosen however there can be multiple.

"Peter Jamieson" wrote:

Something I forgot to ask befo even if you were able to split the
individual ProductIDs out from the ProductLookup field, how would you get
the corresponding Product descriptions during the merge? If the list of
product IDs is small and stable, you could perhaps do it using IF fields and
testing for each ProductID in turn. Otherwise, there's a problem, because
a. the number of Products varies (is there a known limit?)
b. you might be able to do the lookup using a DATABASE field, but these
days, DATABASE fields are more badly behaved than they used to be because
they insert an extra carriage return after the result, even when the result
is a single field

What I would do depends on what technologies I could make use of.
For example, if in this case you are in a position to write a Transact-SQL
procedure/function, I am fairly sure it would be possible to write one that
would take the ProductLookup field as input, and output either a multiline
text field containing "ProductID: Product Description" for each product, or
(not so sure with SQL Server 2000) return a table containing one row with
Product ID and Descriptions. What I'm not at all sure about is whether you
could use the results of those procedures/functions easily in a merge.
Or if you can use Word VBA, then you could for example
c. use Word MailMerge Events to inspect ProductLookup, do the necessary
lookups using ADO, and insert the results into the mail merge main document
pre-merge
d. avoid Word MailMerge altogether and just use ADO to get all the data you
need and build your document using that.

Depending on feasibility etfc. I would have a look at the Transact-SQL
approach first, but in the end there's a lot to be said for heading straight
for (d).

If all that stuff is beyond your scope, I don't know what else to suggest...

Peter Jamieson
"Pip''''n" wrote in message
...
Currently the Data Source is Microsoft SQL Server 2000 and we're using
Word
2007.

The query that i'm using is built dynamically to single out a single
record
by a Deal ID. The database is set up in a way that a Deal can have
multiple
products, but those products are stored as a comma delimted string in a
single field. (Dont ask me why they did this)

The field that contanis the string is dbo.tblDeals.ProductLookup

The document is called from a Classic ASP page that generates the database
view which is called by the word document. In the page they choose the
account and deal that they want to create the contract for.

Here is the SQL:
SELECT dbo.tblDeals.DealID, dbo.tblAccounts.Type,
dbo.tblAccounts.AccountName, dbo.tblAccounts.Address,
dbo.tblAccounts.Address2, dbo.tblAccounts.City,
dbo.tblAccounts.Province, dbo.tblAccounts.PostalCode,
dbo.tblAccounts.Country, dbo.tblProvinces.Name AS ProvinceName,
dbo.tblDeals.DecisionMakerContact, DATEPART(dd, { fn
NOW() }) AS SignupDay, { fn MONTHNAME({ fn NOW() }) } AS SignupMonth,
DATEPART(yyyy,
{ fn NOW() }) AS SignupYear,
dbo.tblContacts.ContactName, dbo.tblContacts.ContactTitle,
dbo.tblContacts.ContactFirstName,
dbo.tblContacts.ContactSuffix,
dbo.tblDeals.ProductLookup FROM dbo.tblDeals INNER JOIN
dbo.tblAccounts ON dbo.tblDeals.AccountLookup =
dbo.tblAccounts.AccountID INNER JOIN
dbo.tblProvinces ON dbo.tblAccounts.Province =
dbo.tblProvinces.Abbreviation LEFT OUTER JOIN
dbo.tblContacts ON dbo.tblDeals.DecisionMakerContact
=
dbo.tblContacts.ContactID
WHERE (dbo.tblDeals.DealID = 1827)

"Peter Jamieson" wrote:

In this case I think you will need to modify your data source (i.e.
create
the data source you really need), and you may find that when you do so,
it
will make sense to use a report generator such as the one in Access to
produce your output rather than Word. If you still need or want to use
Word,
you need to think about the overall merge (e.g. is it a "one-to-many"
merge,
which Word is not really designed to do?)

What is the data source at the moment? Which version of Word?

Peter Jamieson
"Pip''''n" wrote in message
...
I have a mail merge that needs to display various lines of text based on
a
field that contains a list of comma delmited product IDs. Each line
corresponds to a particular product.

Is there a way to split the field to the various IDs and loop through
the
list to show the lines of text that I need?

For Example:

ProductID 1 = Product 1
ProductID 2 = Product 2
ProductID 3 = Product 3

MERGEFIELD ProductLookup contains string "1, 2"

Text in Word Document:

You have purchased the following products:
Product 1 : Description of Product
Product 2 : Description of Product
Product 3 : Description of Product

Thanks in Advance,

Pip'n

[Peter Jamieson]

Well, assuming you are trying to stick to using fields, and assuming you
therefore have a maxmum of 22 different product codes, you may be able to do
it this way - at the moment I'm not in a position to test:
a. enable the Database toolbar in Word, and click the Insert database
button.
b. follow the steps to select your SQL Server view and insert it as a
field. (I am assuming that your mail merge data source is a .odc - you will
probably need to use the same one, or create a suitable .odc and use that.
If you are using an ODBC DSN things may be rather harder, but see how far
you get). Make sure you can update the DATABASE field successfully
c. use Alt-F9 to look at the { DATABASE } field you inserted. It should be
something like this:

{ DATABASE \d "pathname of your .odc" \c "an OLE DB connection string" \s
"SELECT * FROM ""your view name""" \h }
d. Let's suppose the first possible ProductID is "1". Change the SELECT so
that it reads

SELECT PATINDEX('1',dbo.tblDeals.ProductLookup) +
PATINDEX('1,%',dbo.tblDeals.ProductLookup) +
PATINDEX('%,1,%',dbo.tblDeals.ProductLookup) +
PATINDEX('%,1',dbo.tblDeals.ProductLookup) FROM ""your view name""

Change the \h to \#0

e. try executing that - you may have to adjust the syntax. However, the
general idea is that if the string is neither "1", starts with "1,",
contains ",1," nor ends with ",1" the result will be "0"

If you manage to get that to work, what you would then need to do is nest it
inside an IF field, e.g.

{ IF "{ DATABASE all that stuff }" = "0" "" "Product 1: product 1
description" }

f. then, you need to repeat that dfor all the other possible values of
product ID.

Perhaps you can see why I was a little bit reluctant to go down this route,
when it's probably easier to achieve in VBA. But there's no harm in
trying...

Peter Jamieson

"Pip''''n" wrote in message
...
Currently the Descriptions are written for this specific document. They
were
Hard Coded into the Contract document and the Salesmen would just remove
the
ones that they didn't need. This happens in about 4 different locations in
the document.

There are 22 products which correspond to 13 different descriptions (some
products use the same description)

Right now I have it set up with If statements which would work if there
was
only one product chosen however there can be multiple.

"Peter Jamieson" wrote:

Something I forgot to ask befo even if you were able to split the
individual ProductIDs out from the ProductLookup field, how would you get
the corresponding Product descriptions during the merge? If the list of
product IDs is small and stable, you could perhaps do it using IF fields
and
testing for each ProductID in turn. Otherwise, there's a problem, because
a. the number of Products varies (is there a known limit?)
b. you might be able to do the lookup using a DATABASE field, but these
days, DATABASE fields are more badly behaved than they used to be because
they insert an extra carriage return after the result, even when the
result
is a single field

What I would do depends on what technologies I could make use of.
For example, if in this case you are in a position to write a
Transact-SQL
procedure/function, I am fairly sure it would be possible to write one
that
would take the ProductLookup field as input, and output either a
multiline
text field containing "ProductID: Product Description" for each product,
or
(not so sure with SQL Server 2000) return a table containing one row with
Product ID and Descriptions. What I'm not at all sure about is whether
you
could use the results of those procedures/functions easily in a merge.
Or if you can use Word VBA, then you could for example
c. use Word MailMerge Events to inspect ProductLookup, do the necessary
lookups using ADO, and insert the results into the mail merge main
document
pre-merge
d. avoid Word MailMerge altogether and just use ADO to get all the data
you
need and build your document using that.

Depending on feasibility etfc. I would have a look at the Transact-SQL
approach first, but in the end there's a lot to be said for heading
straight
for (d).

If all that stuff is beyond your scope, I don't know what else to
suggest...

Peter Jamieson
"Pip''''n" wrote in message
...
Currently the Data Source is Microsoft SQL Server 2000 and we're using
Word
2007.

The query that i'm using is built dynamically to single out a single
record
by a Deal ID. The database is set up in a way that a Deal can have
multiple
products, but those products are stored as a comma delimted string in a
single field. (Dont ask me why they did this)

The field that contanis the string is dbo.tblDeals.ProductLookup

The document is called from a Classic ASP page that generates the
database
view which is called by the word document. In the page they choose the
account and deal that they want to create the contract for.

Here is the SQL:
SELECT dbo.tblDeals.DealID, dbo.tblAccounts.Type,
dbo.tblAccounts.AccountName, dbo.tblAccounts.Address,
dbo.tblAccounts.Address2, dbo.tblAccounts.City,
dbo.tblAccounts.Province,
dbo.tblAccounts.PostalCode,
dbo.tblAccounts.Country, dbo.tblProvinces.Name AS ProvinceName,
dbo.tblDeals.DecisionMakerContact, DATEPART(dd,
{ fn
NOW() }) AS SignupDay, { fn MONTHNAME({ fn NOW() }) } AS SignupMonth,
DATEPART(yyyy,
{ fn NOW() }) AS SignupYear,
dbo.tblContacts.ContactName, dbo.tblContacts.ContactTitle,
dbo.tblContacts.ContactFirstName,
dbo.tblContacts.ContactSuffix,
dbo.tblDeals.ProductLookup FROM dbo.tblDeals INNER JOIN
dbo.tblAccounts ON dbo.tblDeals.AccountLookup =
dbo.tblAccounts.AccountID INNER JOIN
dbo.tblProvinces ON dbo.tblAccounts.Province =
dbo.tblProvinces.Abbreviation LEFT OUTER JOIN
dbo.tblContacts ON
dbo.tblDeals.DecisionMakerContact
=
dbo.tblContacts.ContactID
WHERE (dbo.tblDeals.DealID = 1827)

"Peter Jamieson" wrote:

In this case I think you will need to modify your data source (i.e.
create
the data source you really need), and you may find that when you do
so,
it
will make sense to use a report generator such as the one in Access to
produce your output rather than Word. If you still need or want to use
Word,
you need to think about the overall merge (e.g. is it a "one-to-many"
merge,
which Word is not really designed to do?)

What is the data source at the moment? Which version of Word?

Peter Jamieson
"Pip''''n" wrote in message
...
I have a mail merge that needs to display various lines of text based
on
a
field that contains a list of comma delmited product IDs. Each line
corresponds to a particular product.

Is there a way to split the field to the various IDs and loop
through
the
list to show the lines of text that I need?

For Example:

ProductID 1 = Product 1
ProductID 2 = Product 2
ProductID 3 = Product 3

MERGEFIELD ProductLookup contains string "1, 2"

Text in Word Document:

You have purchased the following products:
Product 1 : Description of Product
Product 2 : Description of Product
Product 3 : Description of Product

Thanks in Advance,

Pip'n