Re: Mail Merge Using Date Filters With SQL Data Source
- From: "Jonathan Wareham" <JonathanW@xxxxxxxxxxxxxxx>
- Date: Mon, 25 Apr 2005 10:08:48 +0100
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" <pjj@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OF6g7i1RFHA.4068@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <JonathanW@xxxxxxxxxxxxxxx> wrote in message
> news:OCNaBT0RFHA.3988@xxxxxxxxxxxxxxxxxxxxxxx
> > 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
> >
> >
>
>
.
- References:
- Mail Merge Using Date Filters With SQL Data Source
- From: Jonathan Wareham
- Re: Mail Merge Using Date Filters With SQL Data Source
- From: Peter Jamieson
- Mail Merge Using Date Filters With SQL Data Source
- Prev by Date: mail merge
- Next by Date: Re: how do I get Hebrew speller add in in Word?
- Previous by thread: Re: Mail Merge Using Date Filters With SQL Data Source
- Next by thread: Mail Merging changes my data!
- Index(es):
Relevant Pages
|
