Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filter merge data by Date
I have connected a Word merge document to an SQL View successfully.
I would like to be able to filter the merged data by a Date column (e.g. Dates between or dates greater than or dates less than). However none of the filter 'Comparision' options under 'advanced' within the Mail Merge Recipients dialogue do not work. The date column in question can be sorted. It appears to be coming through in Date format (as per its format in the original SQL table. It appears like this: 2007-09-13 00:00:00). I have also tried sending the Date column through in different formats, however although it can sometimes work with the 'Contains' option, this solution is not sophisticated enough. I would prefer a Word based solution because of the Users are not familiar with SQL views etc. Any suggestions gratefully received. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filter merge data by Date
Often Word does not generate the correct syntax when date comparisons are
concerned. Your best bet is /probably/ to convert the dates to a number in YYYYMMDD format in the view, which should allow your users to do equal-to, greater-than, less-than type comparisons reliably, and do "between" using two comparisons (since there is no built-in "between" facility on the Word side of things). The main alternatives are a. you only ever connect using VBA (or another programming language) and Word's OpenDataSource method, building the necessary SQL yourself. In other words, you have to write VBA to capture the criteria from the user, and convert that into SQL. Not very nice. b. the users insert { SKIPIF } fields that compare the dates with the values they want. No good for label merges, can cause problems in other merge types, and non-trivial for them to do. -- Peter Jamieson http://tips.pjmsn.me.uk "John" wrote in message ... I have connected a Word merge document to an SQL View successfully. I would like to be able to filter the merged data by a Date column (e.g. Dates between or dates greater than or dates less than). However none of the filter 'Comparision' options under 'advanced' within the Mail Merge Recipients dialogue do not work. The date column in question can be sorted. It appears to be coming through in Date format (as per its format in the original SQL table. It appears like this: 2007-09-13 00:00:00). I have also tried sending the Date column through in different formats, however although it can sometimes work with the 'Contains' option, this solution is not sophisticated enough. I would prefer a Word based solution because of the Users are not familiar with SQL views etc. Any suggestions gratefully received. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filter merge data by Date
Thank you Peter,
Your first suggestion works the best as it keeps it simple as possible for end users. "Peter Jamieson" wrote: Often Word does not generate the correct syntax when date comparisons are concerned. Your best bet is /probably/ to convert the dates to a number in YYYYMMDD format in the view, which should allow your users to do equal-to, greater-than, less-than type comparisons reliably, and do "between" using two comparisons (since there is no built-in "between" facility on the Word side of things). The main alternatives are a. you only ever connect using VBA (or another programming language) and Word's OpenDataSource method, building the necessary SQL yourself. In other words, you have to write VBA to capture the criteria from the user, and convert that into SQL. Not very nice. b. the users insert { SKIPIF } fields that compare the dates with the values they want. No good for label merges, can cause problems in other merge types, and non-trivial for them to do. -- Peter Jamieson http://tips.pjmsn.me.uk "John" wrote in message ... I have connected a Word merge document to an SQL View successfully. I would like to be able to filter the merged data by a Date column (e.g. Dates between or dates greater than or dates less than). However none of the filter 'Comparision' options under 'advanced' within the Mail Merge Recipients dialogue do not work. The date column in question can be sorted. It appears to be coming through in Date format (as per its format in the original SQL table. It appears like this: 2007-09-13 00:00:00). I have also tried sending the Date column through in different formats, however although it can sometimes work with the 'Contains' option, this solution is not sophisticated enough. I would prefer a Word based solution because of the Users are not familiar with SQL views etc. Any suggestions gratefully received. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge using Access data - date format | Mailmerge | |||
Filter Merge Data by Date Range | Mailmerge | |||
Mail Merge of a Date from Excel Data into Microsoft Word | Microsoft Word Help | |||
Mail Merge Using Date Filters With SQL Data Source | Mailmerge | |||
How do I filter a Dbase date field in a word mail merge documnet? | Mailmerge |