I'm developing a Form Letter engine for my customers which pulls data
from their SQL Server 2000 database. The user clicks a button on the
toolbar which brings them to a data selection form. This form builds a
SQL Query behind the scenes based on their selections. I then Mail
Merge against the query. This works perfectly except when the filter
criteria on the query is a single date field.
Sample VBA Code:
Public Sub ExecuteMailMerge(query As String)
On Error GoTo err_handler
Dim dsn As String
dsn = "My DSN"
With ActiveDocument.MailMerge
.OpenDataSource dsn, LinkToSource:=False, _
SqlStatement:=query
.Execute False
End With
Exit Sub
err_handler:
MsgBox ("Failed to execute mail merge: " + Err.Description)
End Sub
Sample Query:
"SELECT * FROM [View Name] WHERE ( DateField = '9/08/2005 12:00:00 AM'
)"
Sample Query 2:
"SELECT * FROM [View Name] WHERE ( DateField = '9/08/2005' )"
When using a query involving a single date field, I get a "Failed to
execute mail merge: Word was unable to open the data source." error.
I have been able to hack my way past this problem by adding "AND 1=1"
to the end of the query. However, this is still a hack, and I hate
hacks. I'd much rather identify and solve the underlying problem. Any
ideas?
Thanks,
Chris McKenzie
http://weblogs.asp.net/taganov