Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Mail Merge Using Date Filters With SQL Data Source
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 |
#2
|
|||
|
|||
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 "Jonathan Wareham" wrote in message ... 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 |
#3
|
|||
|
|||
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 "Peter Jamieson" wrote in message ... 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 "Jonathan Wareham" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specific Email Merge w/ Specific Attachements | Mailmerge | |||
Mail Merge Issue With Office 97 - Excel Data Source | Mailmerge | |||
Mail merge data source problem | Mailmerge | |||
Mail merge error occurs when filtering Excel data source | Mailmerge | |||
Merge Data Source path | Mailmerge |