In essence, when Word connects to a datasource, it sends the query as an SQL
SELECT statement (even if the data source is a Word document).
Assuming you are using Word VBA, what you need to do is construct the
correct SQL for use in a call to OpenDataSource or the correct SQL to assign
to ActiveDocument.MailMerge.DataSource.QueryString.
Of those two options I think the former is probably a bit more reliable.
How easy that is to do depends on how general-purpose your code needs to be.
If the user has no control over the data source, and cannot use other
sorting/filtering options (e.g. via the Query Options in Word 2000 and
earlier or the advanced options in the Edit Recipients dialog box in Word
2002 and later), then it should be reasonably easy. If the user has more
flexibility, things become much more difficult because
a. the syntax of the SQL may need to be different depending on the data
source (the built-in dialect that is used to get data from a Word data
source is not the same as Jet SQL, for example)
b. it may be more difficult to insert your bits of SQL correctly.
Assuming the simple situation, typically the SQL that Word issues for an
unfiltered, unsorted data source is quite simple - something like
SELECT * FROM tablename
where tablename may be a file path name, or may be enclosed in some form of
quotes (e.g. [] for Jet SQL), and so on.
Suppose what your user needs to do is enter a number and you want to select
records where the field xyz is greater than that number, then the required
SQL would look like
SELECT * FROM tablename WHERE xyz > thenumber
so all you would need to do is display a VBA Userform (or e.g. use Inputbox)
to get the value and add it to the end of a SELECT string consisting of
"SELECT * FROM tablename WHERE xyz > "
It's advisable to generate the SQL required by the type of data source you
are using, and follow that syntax when constructing your own queries. For
example, Word tends to insert more brackets around expressions than are
probably necessary, but it is probably worth playing safe and following its
pattern. Unfortunately, in Word 2002 it can be difficult to inspect the
Querystring because of an error in Word, but you can try setting up the
connection, saving the document in HTML format, then opening it as a text
file and looking for the SELECT statement Word has created.
