Mail Merge Using Date Filters With SQL Data Source

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jonathan Wareham - 22 Apr 2005 15:08 GMT

Hi,

We have a problem using the filter data source option when mail merging
using an SQL server 2000 view. Filtering on dates does not appear to work.
It is as if Word is not interpreting the date in the correct format and any
filters applied to date fields are rejected by the mail merge wizard.
Filters on other data types work fine.

For example, we have a column called 'Date Sold', the dates are stored as
YYYY-MM-DD HH:MM:SS (2004-12-14 00:00:00). We click on the down arrow to
Advanced filter, select the Date Sold field and set up a greater than
filter. We entered the date in the compared to field in the same format as
it is presented, on clicking OK the query appears to be run, however, the
records have not been filtered as expected and when reexamining the Filter
and Sort window the date filter has disappeared.

Any help with this problem will be greatly appreciated.

Regards,

Jonathan

Reply to this Message

Peter Jamieson - 22 Apr 2005 17:31 GMT

Yes, assuming you are using Word 2002 or later, unfortunately date filtering
no longer works at all well, although there is no reason why it should not.
I suspect that Word generates the wrong SQL, tries it, is rejected and
silently behaves as if the criterion had never been created, but I cannot be
sure.

Options include:
a. if you can, create a view that returns the correct data. That is only
really possible if the date criterion can be determined programmatically in
some way.
b. you could /try/ making a view that returns the date in text string
format using the format YYYY-MM-DD. Then the user /should/ be able to enter
their comparison using YYYY-MM-DD format, Word should treat the comparison
as a text comparison, not a date comparison, and it should still work. I
haven't tested that recently but if it works it's by far the simplest
approach IMO
c. use VBA to modify the SQL. e.g. if you are using a .odc file called
c:\myodcs\mydb.odc and you are getting your data from a table called
"sales", try:

Sub GetFilteredData()
' Substitute the date you need
ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\myodcs\mydb.odc", _
SQLStatement:="SELECT * FROM [sales] WHERE [Date Sold] > '2005-02-01'"

End Sub

Obviously if the date criterion is supposed to be user-definable, that is no
use to most end users and you would need to provide your own Userform or
some such to get the date from the user, validate it and use it to generate
the string "SQLStatement"

d. change your connection method to ODBC (assuming you are using OLEDB,
which you are if you created a .odc when you connected). This will require
an ODBC DSN on each machine that needs to access this data source. Then
connect via SQL Query (in Word 2002/2003 the option is on the Tools menu in
the top right corner of the Select Data Source menu). That is only possible
if MS Query has been installed, and using MS Query is not a walk in the park
for end users either. However, once you have gone that route, any attempt to
use Edit Recipients to specify sorts and filters should result in Word
starting MS Query and allowing the user to specify criteria. All I can say
is that it works here.

Peter Jamieson

> Hi,
>
[quoted text clipped - 20 lines]
>
> Jonathan

Reply to this Message

Jonathan Wareham - 25 Apr 2005 10:08 GMT

Many thanks Peter, I did actually look into the problem further by
monitoring the SQL Word sends to the server and yes it is not correct SQL
for the query.

I have changed the SQL view to return the dates as per your suggestion b.,
and as long as my end users are happy entering the date criteria in this
format then I'm home and dry.

Jonathan

> Yes, assuming you are using Word 2002 or later, unfortunately date filtering
> no longer works at all well, although there is no reason why it should not.
[quoted text clipped - 66 lines]
> >
> > Jonathan

Reply to this Message