MailMerge SQL QueryString

[Peter Wilde]

I'm trying to restrict the data returned from preset
Microsoft SQL Query by using the
ActiveDocument.MailMerge.DataSource.QueryString
statement. The data source is obtained from a Delphi App
using DBISAM files. The ODBC works fine and I can read
the fields in Microsoft SQL Query but I want to create a
word Mailmerge document with a Userform containing two
date fields which are then included in the SQL statement
used at mail merge time. When I issue the statement
ActiveDocument.MailMerge.DataSource.QueryString, it
simply reads the SQL statement rather than sending the
statement to Microsoft Query.
Any suggestions?
Peter Wilde.

 

[Peter Jamieson]

What is the query you start with, and what are you assigning to
..QueryString?

Which version of Word?

(SImple tests with an ODBC connection to an Access database from Word 2000
work as expected here - i.e. you assign the new SELECT string to
..QueryString and Word goes off and requeries the data.)

BTW, as far as I know, after you have set of the connection using MS Query,
Word gets the data directly using ODBC - it doesn't go through MS Query each
time it needs the data.

 

[Guest]

Thanks for your help. I'm using Word 2000 but I want the
document to run on Word 97.
The SQL statement is as follows;
SELECT
bookings.ClientID,bookings.Date,bookings.StartTime,
bookings.Description, bookings.Notes, Clients.FirstName,
Clients.LastName, Clients.Address1, Clients.City,
Clients.State, Clients.Postal, Clients.HomePhone,
Clients.WorkPhone
FROM "C:\AppData"\bookings bookings, "C:\AppData"\Clients
Clients
WHERE Clients.ClientID = bookings.ClientID AND
((bookings.Description='NP') AND (bookings.Date Between
{d '2004-01-01'} And {d '2010-12-31'}))
ORDER BY bookings.Date

I'm trying to restrict the mail merge records between 2
dates which are obtained by the user via a dialogue box
with 2 textboxs. Thanks for any suggestions you have

Peter Wilde

 

[Peter Jamieson]

OK, I don't have the same database software you have, but using an Access
..mdb with suitable tables and fields and similar query text I do not have
problems here with Word 97/Access 97 and ODBC (on Win98, as it happens).

So the thing works in principle, and in particular
a. the query is not too long (510 characters is the limit)
b. the ODBC escapes for the dates - { d '2004-01-01' } etc. work correctly.

So the only things I can thing of are:
a. is the query syntax correct for your data source - in particular, the
quoting is unusual in:

FROM "C:\AppData"\bookings bookings, "C:\AppData"\Clients Clients

(
I would expect something more like

FROM "C:\AppData\bookings" bookings, "C:\AppData\Clients" Clients

and possibly to have to include file extensions in the table names. I didn't
replicate this part in my query since I only needed

FROM bookings bookings, Clients Clients

You might also have to use single quotes.
)

b. does your ODBC driver support the ODBC escape syntax for dates? If you
remove the date constraint, does the modified querystring succeed or fail?