[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