How to enter a Date in Advanced | Filter Records

[Loretta Johnson]

I need to filter my data source to include only records
that have been modified since a specific date. I have a
ModifiedDate field that is defined in SQL Server as a
datetimestamp. I have tried every way I can think of to
enter the date to get the records filtered. However, I
always end up with all of the records showing in the Mail
Recipients dialog.

Does anyone know the proper way to enter dates when
filtering? The dates are displaying in YYYY-MM-DD
HH:MM:SS .... and so on.

Thank you in advance.

 

[Cindy M -WordMVP-]

Hi Loretta,

I can't recall having ever tested this with SQL Server,
but...

generally, the Query Options dialog box will only work with
dates if they're entered in "good old" U.S. date format:
mm/dd/yyyy

If that doesn't work for you, then the best thing would
probably be to create a macro (VBA) interface to set the
QueryString property for the merge document. for that, you
should be able to use an SQL Select statement that
corresponds to what you'd generally use with SQL Server.

I need to filter my data source to include only records
that have been modified since a specific date. I have a
ModifiedDate field that is defined in SQL Server as a
datetimestamp. I have tried every way I can think of to
enter the date to get the records filtered. However, I
always end up with all of the records showing in the Mail
Recipients dialog.

Does anyone know the proper way to enter dates when
filtering? The dates are displaying in YYYY-MM-DD
HH:MM:SS .... and so on.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Peter Jamieson]

Hi Cindy,

Just as follow-up, I suspect in Word 2003 and probably Word 2002, the macro
approach is the only option if you are connecting via OLEDB. The problem
appears to be that
a. The Query options dialog box will only allow you to enter something that
it thinks looks like a date - 1994-09-14, 14/09/1994, etc.
b. But whatever you put, Word appears simply to discard it
c. in VBA, none of the date literal formats that SQL should really accept
as valid will work except the "ODBC escape" format. In other words, none of
the following work:

WHERE mydate = 19940914
WHERE mydate = '1994-09-14'
WHERE mydate = '1994-09-14 00:00:00'
WHERE mydate = '14/09/1994'
WHERE mydate = '09/14/1994'

The following do work (at least in the sense that SQL Server recognises the
syntax as valid)

WHERE mydate = { d '1994-09-14' }
WHERE mydate = { ts '1994-09-14 00:00:00' }

d. i.e. in all probability Word does not convert the user specified options
in Query options into a date format that SQL Server (or the OLEDB provider)
recognises, but nor does it allow { d '1994-09-14' } into the Query options
dialog, which would at least give an end user /a/ way of specifying a date.
..
However, in Word 2003 at any rate, things seem to work rather better if you
connect via ODBC. But since that involves all that MS Query stuff it is
somewhat harder (The main trick is to use the Tools menu in the top right of
the Select Data Source dialog box to start MS Query, create a suitable SQL
Server DSN, and take it from there). For an ODBC connection the Mail Merge
Recipients dialog works rather differently. Selecting a specific datetime
value to match from the dropdown at the top of a date column still does
nothing at all. But if you click Advanced, you go to MS Query, where using
the Criteria menu does allow you to specify date comparisons in a way that
works, all other things being equal.

 

[Cindy M -WordMVP-]

Hi Peter,

Just as follow-up, I suspect in Word 2003 and probably Word 2002, the macro
approach is the only option if you are connecting via OLEDB. The problem
appears to be that...

Cindy