MailMerge merges but not data...

[T]

Macro in Word 2002..

When my mailmerge is executed, a new form letter is
created and 183 pages (When the query is ran in SQL
Server, 183 contacts are return.) are dislayed, but with
no data. What am I doing wrong?

 

[Peter Jamieson]

Do the columns you are trying to include in your output (i.e. in the
MERGEFIELDS) have Unicode data types? If so, they may appear as blank, but I
would only expect that with Word 2002 if you are connecting to SQL Server
using ODBC, not OLEDB. Do you know which method you are using? (If you are
issuing an OpenDataSource command in VBA, let us know what parameters you
are passing to it). If you have to use ODBC, you will need to create a query
or view that uses the CAST or CONVERT functions to convert Unicode strings
to ANSI strings, with possible loss of any non-ANSI character data.

 

[Guest]

-----Original Message-----
Do the columns you are trying to include in your output (i.e. in the
MERGEFIELDS) have Unicode data types? If so, they may appear as blank, but I
would only expect that with Word 2002 if you are connecting to SQL Server
using ODBC, not OLEDB. Do you know which method you are using? (If you are
issuing an OpenDataSource command in VBA, let us know what parameters you
are passing to it). If you have to use ODBC, you will need to create a query
or view that uses the CAST or CONVERT functions to convert Unicode strings
to ANSI strings, with possible loss of any non-ANSI character data.

--
Peter Jamieson
MS Word MVP




Peter,

Thank you for your reply. Below is my test macro...

Also, I'm using Word 2002 and my client has 2000. We'll
there be any quirks between the two versions?

Thanks in advance,
T. Wade Nolte

Sub Test()

Dim CityCode
Dim cmd

CityCode = InputBox("Please, enter a city code to merge:")
cmd = "SELECT FirstName, LastName, City, State, Zip FROM
[EUQ_CurrentOwner] WHERE City = '" & CityCode & "' "

With ActiveDocument.MailMerge

.OpenDataSource Name:="", _

Connection:="DSN=BCS;DATABASE=bcs;uid=;pwd=", _
SQLStatement:=cmd, _
SubType:=wdMergeSubTypeWord2000


' Add merge fields.
.Fields.Add Selection.Range, "FirstName"
Selection.TypeText "Mr. "
.Fields.Add Selection.Range, "LastName"
Selection.TypeParagraph
.Fields.Add Selection.Range, "City"
Selection.TypeText ", "
.Fields.Add Selection.Range, "State"
Selection.TypeParagraph
.Fields.Add Selection.Range, "Zip"
Selection.TypeParagraph

.Destination = wdSendToNewDocument
.Execute
End With


End Sub

 

[Peter Jamieson]

OK, you are using ODBC to get your data so if the data columns are Unicode
you will experience the problems I mentioned. (The columns are likely to be
Unicode if you used the Upsizing Wizard to create your SQL Server database
from an Access database, but the database designer could just have used
Unicode because it's normally the right thing to do these days.)

So first you need to establish whether that is definitely the case, and
whether changing to non-Unicode definitely fixes the problem. If you
definitely do not need Unicode, another possible solution other than the
ones I mentioned is to change the data type of the offending columns.

The sort of function you need to convert Unicode data to non-Unicode data in
a SQL view is

CAST(MyTable.MyField AS CHAR) AS MyField

Also, I'm using Word 2002 and my client has 2000. We'll
there be any quirks between the two versions?

Well, in Word 2002 you should be able to work around this problem by using
an OLEDB connection (you'll need to go through the New SQL Server connection
process to create a .odc file, and modify your OpenDataSource). But that
option is simply not available in Word 2000. So the ODBC option is the only
one which wil work with both versions.

The name, connection strings, query etc. should be OK for both versions of
Word but Word 2000 VBA does not allow the use of the Subtype parameter,
whereas to use ODBC in this way in Word 2002 you must use it. Typically you
will need to create one sub/function for the Word 2000 version and one for
the Word 2002 version, and call the correct one depending on which version
of Word you are using.

--
Peter Jamieson
MS Word MVP

-----Original Message-----
Do the columns you are trying to include in your output (i.e. in the
MERGEFIELDS) have Unicode data types? If so, they may appear as blank, but I
would only expect that with Word 2002 if you are connecting to SQL Server
using ODBC, not OLEDB. Do you know which method you are using? (If you are
issuing an OpenDataSource command in VBA, let us know what parameters you
are passing to it). If you have to use ODBC, you will need to create a query
or view that uses the CAST or CONVERT functions to convert Unicode strings
to ANSI strings, with possible loss of any non-ANSI character data.

--
Peter Jamieson
MS Word MVP




Peter,

Thank you for your reply. Below is my test macro...

Also, I'm using Word 2002 and my client has 2000. We'll
there be any quirks between the two versions?

Thanks in advance,
T. Wade Nolte

Sub Test()

Dim CityCode
Dim cmd

CityCode = InputBox("Please, enter a city code to merge:")
cmd = "SELECT FirstName, LastName, City, State, Zip FROM
[EUQ_CurrentOwner] WHERE City = '" & CityCode & "' "

With ActiveDocument.MailMerge

.OpenDataSource Name:="", _

Connection:="DSN=BCS;DATABASE=bcs;uid=;pwd=", _
SQLStatement:=cmd, _
SubType:=wdMergeSubTypeWord2000


' Add merge fields.
.Fields.Add Selection.Range, "FirstName"
Selection.TypeText "Mr. "
.Fields.Add Selection.Range, "LastName"
Selection.TypeParagraph
.Fields.Add Selection.Range, "City"
Selection.TypeText ", "
.Fields.Add Selection.Range, "State"
Selection.TypeParagraph
.Fields.Add Selection.Range, "Zip"
Selection.TypeParagraph

.Destination = wdSendToNewDocument
.Execute
End With


End Sub

 

[Guest]

Peter,

You are the man! thank you so much, you just made me yell
out load. All my SQL 2K column definations were nvarchars,
I cast them as varchars, and we're money.

Also, thanks for the tip on the two version differences.

Travas

-----Original Message-----
OK, you are using ODBC to get your data so if the data columns are Unicode
you will experience the problems I mentioned. (The columns are likely to be
Unicode if you used the Upsizing Wizard to create your SQL Server database
from an Access database, but the database designer could just have used
Unicode because it's normally the right thing to do these days.)

So first you need to establish whether that is definitely the case, and
whether changing to non-Unicode definitely fixes the problem. If you
definitely do not need Unicode, another possible solution other than the
ones I mentioned is to change the data type of the offending columns.

The sort of function you need to convert Unicode data to non-Unicode data in
a SQL view is

CAST(MyTable.MyField AS CHAR) AS MyField

Also, I'm using Word 2002 and my client has 2000. We'll
there be any quirks between the two versions?

Well, in Word 2002 you should be able to work around this problem by using
an OLEDB connection (you'll need to go through the New SQL Server connection
process to create a .odc file, and modify your OpenDataSource). But that
option is simply not available in Word 2000. So the ODBC option is the only
one which wil work with both versions.

The name, connection strings, query etc. should be OK for both versions of
Word but Word 2000 VBA does not allow the use of the Subtype parameter,
whereas to use ODBC in this way in Word 2002 you must use it. Typically you
will need to create one sub/function for the Word 2000 version and one for
the Word 2002 version, and call the correct one depending on which version
of Word you are using.

--
Peter Jamieson
MS Word MVP

-----Original Message-----
Do the columns you are trying to include in your output (i.e. in the
MERGEFIELDS) have Unicode data types? If so, they may appear as blank, but I
would only expect that with Word 2002 if you are connecting to SQL Server
using ODBC, not OLEDB. Do you know which method you are using? (If you are
issuing an OpenDataSource command in VBA, let us know what parameters you
are passing to it). If you have to use ODBC, you will need to create a query
or view that uses the CAST or CONVERT functions to convert Unicode strings
to ANSI strings, with possible loss of any non-ANSI character data.

--
Peter Jamieson
MS Word MVP

Macro in Word 2002..

When my mailmerge is executed, a new form letter is
created and 183 pages (When the query is ran in SQL
Server, 183 contacts are return.) are dislayed, but with
no data. What am I doing wrong?


Peter,

Thank you for your reply. Below is my test macro...

Also, I'm using Word 2002 and my client has 2000. We'll
there be any quirks between the two versions?

Thanks in advance,
T. Wade Nolte

Sub Test()

Dim CityCode
Dim cmd

CityCode = InputBox("Please, enter a city code to merge:")
cmd = "SELECT FirstName, LastName, City, State, Zip FROM
[EUQ_CurrentOwner] WHERE City = '" & CityCode & "' "

With ActiveDocument.MailMerge

.OpenDataSource Name:="", _

Connection:="DSN=BCS;DATABASE=bcs;uid=;pwd=", _
SQLStatement:=cmd, _
SubType:=wdMergeSubTypeWord2000


' Add merge fields.
.Fields.Add Selection.Range, "FirstName"
Selection.TypeText "Mr. "
.Fields.Add Selection.Range, "LastName"
Selection.TypeParagraph
.Fields.Add Selection.Range, "City"
Selection.TypeText ", "
.Fields.Add Selection.Range, "State"
Selection.TypeParagraph
.Fields.Add Selection.Range, "Zip"
Selection.TypeParagraph

.Destination = wdSendToNewDocument
.Execute
End With


End Sub

.

 

[T]

Peter,

Thanks again for your help...

What I'm running into now is the is issue with the two
versions.

I added an If Then Else statement and test for
Application.version.

If version = 10 then I use SubType Else I use only
Connection & SQL.

I thought that would work but it looks like VBA is still
trying to parse the subtype and then bombs for 2000.

Would this be true?

Thanks,
Travas

-----Original Message-----
OK, you are using ODBC to get your data so if the data columns are Unicode
you will experience the problems I mentioned. (The columns are likely to be
Unicode if you used the Upsizing Wizard to create your SQL Server database
from an Access database, but the database designer could just have used
Unicode because it's normally the right thing to do these days.)

So first you need to establish whether that is definitely the case, and
whether changing to non-Unicode definitely fixes the problem. If you
definitely do not need Unicode, another possible solution other than the
ones I mentioned is to change the data type of the offending columns.

The sort of function you need to convert Unicode data to non-Unicode data in
a SQL view is

CAST(MyTable.MyField AS CHAR) AS MyField

Also, I'm using Word 2002 and my client has 2000. We'll
there be any quirks between the two versions?

Well, in Word 2002 you should be able to work around this problem by using
an OLEDB connection (you'll need to go through the New SQL Server connection
process to create a .odc file, and modify your OpenDataSource). But that
option is simply not available in Word 2000. So the ODBC option is the only
one which wil work with both versions.

The name, connection strings, query etc. should be OK for both versions of
Word but Word 2000 VBA does not allow the use of the Subtype parameter,
whereas to use ODBC in this way in Word 2002 you must use it. Typically you
will need to create one sub/function for the Word 2000 version and one for
the Word 2002 version, and call the correct one depending on which version
of Word you are using.

--
Peter Jamieson
MS Word MVP

-----Original Message-----
Do the columns you are trying to include in your output (i.e. in the
MERGEFIELDS) have Unicode data types? If so, they may appear as blank, but I
would only expect that with Word 2002 if you are connecting to SQL Server
using ODBC, not OLEDB. Do you know which method you are using? (If you are
issuing an OpenDataSource command in VBA, let us know what parameters you
are passing to it). If you have to use ODBC, you will need to create a query
or view that uses the CAST or CONVERT functions to convert Unicode strings
to ANSI strings, with possible loss of any non-ANSI character data.

--
Peter Jamieson
MS Word MVP

Macro in Word 2002..

When my mailmerge is executed, a new form letter is
created and 183 pages (When the query is ran in SQL
Server, 183 contacts are return.) are dislayed, but with
no data. What am I doing wrong?


Peter,

Thank you for your reply. Below is my test macro...

Also, I'm using Word 2002 and my client has 2000. We'll
there be any quirks between the two versions?

Thanks in advance,
T. Wade Nolte

Sub Test()

Dim CityCode
Dim cmd

CityCode = InputBox("Please, enter a city code to merge:")
cmd = "SELECT FirstName, LastName, City, State, Zip FROM
[EUQ_CurrentOwner] WHERE City = '" & CityCode & "' "

With ActiveDocument.MailMerge

.OpenDataSource Name:="", _

Connection:="DSN=BCS;DATABASE=bcs;uid=;pwd=", _
SQLStatement:=cmd, _
SubType:=wdMergeSubTypeWord2000


' Add merge fields.
.Fields.Add Selection.Range, "FirstName"
Selection.TypeText "Mr. "
.Fields.Add Selection.Range, "LastName"
Selection.TypeParagraph
.Fields.Add Selection.Range, "City"
Selection.TypeText ", "
.Fields.Add Selection.Range, "State"
Selection.TypeParagraph
.Fields.Add Selection.Range, "Zip"
Selection.TypeParagraph

.Destination = wdSendToNewDocument
.Execute
End With


End Sub

.

 

[Peter Jamieson]

In VBA, it should be enough to put each OpenDataSource call in a different
/sub or function/. Just having an If Then Else isn't enough.

In VB, it's a different question because the compiler works differently.

--
Peter Jamieson
MS Word MVP

Peter,

Thanks again for your help...

What I'm running into now is the is issue with the two
versions.

I added an If Then Else statement and test for
Application.version.

If version = 10 then I use SubType Else I use only
Connection & SQL.

I thought that would work but it looks like VBA is still
trying to parse the subtype and then bombs for 2000.

Would this be true?

Thanks,
Travas

-----Original Message-----
OK, you are using ODBC to get your data so if the data columns are Unicode
you will experience the problems I mentioned. (The columns are likely to be
Unicode if you used the Upsizing Wizard to create your SQL Server database
from an Access database, but the database designer could just have used
Unicode because it's normally the right thing to do these days.)

So first you need to establish whether that is definitely the case, and
whether changing to non-Unicode definitely fixes the problem. If you
definitely do not need Unicode, another possible solution other than the
ones I mentioned is to change the data type of the offending columns.

The sort of function you need to convert Unicode data to non-Unicode data in
a SQL view is

CAST(MyTable.MyField AS CHAR) AS MyField

Also, I'm using Word 2002 and my client has 2000. We'll
there be any quirks between the two versions?

Well, in Word 2002 you should be able to work around this problem by using
an OLEDB connection (you'll need to go through the New SQL Server connection
process to create a .odc file, and modify your OpenDataSource). But that
option is simply not available in Word 2000. So the ODBC option is the only
one which wil work with both versions.

The name, connection strings, query etc. should be OK for both versions of
Word but Word 2000 VBA does not allow the use of the Subtype parameter,
whereas to use ODBC in this way in Word 2002 you must use it. Typically you
will need to create one sub/function for the Word 2000 version and one for
the Word 2002 version, and call the correct one depending on which version
of Word you are using.

--
Peter Jamieson
MS Word MVP

-----Original Message-----
Do the columns you are trying to include in your output
(i.e. in the
MERGEFIELDS) have Unicode data types? If so, they may
appear as blank, but I
would only expect that with Word 2002 if you are
connecting to SQL Server
using ODBC, not OLEDB. Do you know which method you are
using? (If you are
issuing an OpenDataSource command in VBA, let us know
what parameters you
are passing to it). If you have to use ODBC, you will
need to create a query
or view that uses the CAST or CONVERT functions to
convert Unicode strings
to ANSI strings, with possible loss of any non-ANSI
character data.

--
Peter Jamieson
MS Word MVP

Macro in Word 2002..

When my mailmerge is executed, a new form letter is
created and 183 pages (When the query is ran in SQL
Server, 183 contacts are return.) are dislayed, but with
no data. What am I doing wrong?


Peter,

Thank you for your reply. Below is my test macro...

Also, I'm using Word 2002 and my client has 2000. We'll
there be any quirks between the two versions?

Thanks in advance,
T. Wade Nolte


Sub Test()

Dim CityCode
Dim cmd

CityCode = InputBox("Please, enter a city code to merge:")
cmd = "SELECT FirstName, LastName, City, State, Zip FROM
[EUQ_CurrentOwner] WHERE City = '" & CityCode & "' "

With ActiveDocument.MailMerge

.OpenDataSource Name:="", _

Connection:="DSN=BCS;DATABASE=bcs;uid=;pwd=", _
SQLStatement:=cmd, _
SubType:=wdMergeSubTypeWord2000


' Add merge fields.
.Fields.Add Selection.Range, "FirstName"
Selection.TypeText "Mr. "
.Fields.Add Selection.Range, "LastName"
Selection.TypeParagraph
.Fields.Add Selection.Range, "City"
Selection.TypeText ", "
.Fields.Add Selection.Range, "State"
Selection.TypeParagraph
.Fields.Add Selection.Range, "Zip"
Selection.TypeParagraph

.Destination = wdSendToNewDocument
.Execute
End With


End Sub

.