Filter merge data by Date

[John]

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.

 

[Peter Jamieson]

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.

 

[John]

Thank you Peter,

Your first suggestion works the best as it keeps it simple as possible for
end users.