query filter using date from foxpro 2.6 database as data source

[pearle]

Hi All,
I have a word 2000 mailmerge that uses a foxpro 2.6 database as th
data source. I need to be able to query based on a date field. When
try to enter the date the merge gives me all of the records, an
ignores the filter (ie. foxdate equal to 01/25/2004). I have trie
different formats for the date but none seem to work.

Any help is greatly appreciated.

:

 

[Peter Jamieson]

Unfortunately I suspect that Word does not generate SQL that the FoxPro ODBC
driver (which is where Word gets its data from) will understand. Usually,
Word 2000 will use # characters to enclose a date literal and will generate
code such as

SELECT * FROM mytable WHERE ((mydate > #01/01/2003#))

with the FoxPro driver, as you say "nothing happens" and in fact any
previous query is left in place.

Also, I don't have a simple solution. You could try
a. connecting with the dBASE ODBC driver instead, which accepts the #
syntax. (Check the "Select method" box in Open Data Source and choose e.g.
"dBASE files via ODBC". However, in my experience the "dBASE" and "FoxPro"
recognise different versions of the .dbf format and this probbly will not
work.
b. using MS Query to form your query, if it is being offered as an option
when you try to gointo Query Options. It is more complicated and you may
still have difficulties, but it should at least get the SQL right.
c. if it is just you using this merge you can try modifying the query
directly in VBA. e.g.
open up the VBA editor, go into the Immediate pane, and type
print activedocument.mailmerge.datasource.querystring
Suppse it then reports
SELECT * FROM mytable
then enter
activedocument.mailmerge.datasource.querystring = "SELECT * FROM mytable
WHERE (( mydate > { d '2003-01-01' }))

obviously altering the field names and operators to be what you need. The
date should be in YYYY-MM-DD format.
d. exporting the data to something that allows Word to do date comparisons
correctly