MailMerge.OpenDataSource , ...,SQLStatement,SQLStatement1

[Gunnar Nygaard]

HOW DO I SPLIT LONG SQL-STATEMENT IN
ActiveDocument.MailMerge.OpenDataSource ,,,,,

(Please forgive if I've posted to a group where this is too far outside
it's subject)
I am building an Access-database from which I want to pick some data and
send the
query to Word for MailMerge in some chosen template

I'm having a hard time getting Word97 to accept my SQLStatement when it
exceeds
255 characters. The helpfile tells me that SQLStatement and
SQLStatement1 are
optional, and that SQLStatement1 is there in case the SQL-string exceeds
255 chars.

I have tried to split the initial string at 255 chars and call the
second part of it
strSQL2. Both strings inside chr(34) (it seems like Word wants it that
way)

I have not found very much documentation on this.
Maybe some of You can give me a tip to get the show on the road?

I would greatly appreciate Your helping response!
(preferably by e-mail, but that's up to You!)

Regards
Gunnar Nygaard
Norway

******************************************

' This is (modified) what works on short SQL-strings
' But not on long strings:

Function MergeToWord(strQRY as String)
Dim This and That
Dim MergeDoc As Word.Application

strSQL = "SELECT * FROM qrySomeQuery WHERE Name ='Somename1' _
OR Name ='Somename1'....etc."

'Stringlength(strSQL) is > 255 chars

If strSQL = "" Then
strSQL = Chr(34) & "SELECT * FROM " & qrySomeQuery & Chr(34)
Else
If Len(strSQL) > 253 Then
strSQL2 = Chr(34) & MID(strSQL, 254) & Chr(34)
strSQL = Chr(34) & Left(strSQL, 253) & Chr(34)
Else
strSQL2 = ""
End If
End If

' At this point
' strSQL is 255 chars long and
' strSQL2 is less than 255 chars long
' Both starting and ending with Chr(34) (")

Set MergeDoc = CreateObject("Word.Application")

With MergeDoc
.Application.Visible = True
.Documents.Add Template:="C:\MergeTemplates\SomeDot.dot

.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

.ActiveDocument.MailMerge.OpenDataSource Name:="C:\MyDBs\MyDB.mdb",
_
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=True, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="QUERY qrySomeQuery",
SQLStatement:= _
strSQL, SQLStatement1:=strSQL2
End With
Set MergeDoc = Nothing
End Function

--
Please remove 123 from my return address!
This has been added to fight spam!

[Gunnar Nygaard]

Gunnar Nygaard wrote:

> HOW DO I SPLIT LONG SQL-STATEMENT IN
> ActiveDocument.MailMerge.OpenDataSource ,,,,,
>

> (Please forgive if I've posted to a group where this question is outside
> the group's subject)

> I am building an Access-database from which I want to pick some data and
> send the
> query to Word for MailMerge in some chosen template
>
> I'm having a hard time getting Word97 to accept my SQLStatement when it
> exceeds
> 255 characters. The helpfile tells me that SQLStatement and
> SQLStatement1 are
> optional, and that SQLStatement1 is there in case the SQL-string exceeds
> 255 chars.
>
> I have tried to split the initial string at 255 chars and call the
> second part of it
> strSQL2. Both strings inside chr(34) (it seems like Word wants it that
> way)
>
> I have not found very much documentation on this.
> Maybe some of You can give me a tip to get the show on the road?
>
> I would greatly appreciate Your helping response!
> (preferably by e-mail, but that's up to You!)
>
> Regards
> Gunnar Nygaard
> Norway
>
> ******************************************
>
> ' This is (modified) what works on short SQL-strings
> ' But not on long strings:
>

> Function MergeToWord(strSQL as String)

> Dim This and That
> Dim MergeDoc As Word.Application
>

> ' ( strSQL = "SELECT * FROM qrySomeQuery WHERE Name ='Somename1' _
> ' OR Name ='Somename1'....etc.")