Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
[email protected] KB9REU@gmail.com is offline
external usenet poster
 
Posts: 3
Default Mail merge with Word 2003 and SQL 2005 on a date and time field.

I'm trying to do a mail merge from Word 2003 and MS SQL Server 2005.
Previously I used Word 2003 and Access 2003 but I have since migrated
the data to SQL and still use Access as a front end for this app. The
app used to call a Word 2003 document through a hyperlink and run a
query to pull all records. The user would then select Mail Merge
Recipients and narrow the query down to just the date they needed.
This no longer works for me, it just returns all records as if the
query never ran. When I viewed the SQL server trace file I see that
there are #'s surrounding the date field instead of '. I've tried
creating a new datasource in word and a new mail merge document but
the query still shows #'s as being sent.

The field in the SQL server is smallDateTime.

exec sp_cursoropen @p1 output,N'SELECT * FROM "Violators" WHERE
"Letter 1 Date" = #06/11/2007#',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5


Thank you for your help,
Alan

  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Mail merge with Word 2003 and SQL 2005 on a date and time field.

Word makes it pretty difficult to do date selections now because it doesn't
generate the required SQL.

If you want to query the SQL Server database directly, your best bet is
probably to create a view that returns the date in either text format, e.g.
'20070611' or numeric format, e.g. 20070611, and let the user select from
those dates instead. You should be able to use e.g.
CONVERT(nchar,mydatefieldname,112) As MyDateName
to do that.

Peter Jamieson
wrote in message
oups.com...
I'm trying to do a mail merge from Word 2003 and MS SQL Server 2005.
Previously I used Word 2003 and Access 2003 but I have since migrated
the data to SQL and still use Access as a front end for this app. The
app used to call a Word 2003 document through a hyperlink and run a
query to pull all records. The user would then select Mail Merge
Recipients and narrow the query down to just the date they needed.
This no longer works for me, it just returns all records as if the
query never ran. When I viewed the SQL server trace file I see that
there are #'s surrounding the date field instead of '. I've tried
creating a new datasource in word and a new mail merge document but
the query still shows #'s as being sent.

The field in the SQL server is smallDateTime.

exec sp_cursoropen @p1 output,N'SELECT * FROM "Violators" WHERE
"Letter 1 Date" = #06/11/2007#',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5


Thank you for your help,
Alan


  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
[email protected] KB9REU@gmail.com is offline
external usenet poster
 
Posts: 3
Default Mail merge with Word 2003 and SQL 2005 on a date and time field.

Thanks Peter.

I went with your method to return the date but instead of 112, I used
10. I made a view on the SQL server and then had to make some new
Office Data Sources in Word 2003 to connect to it, but that works
perfectly. And I did some SQL profiling and had the server throw in
an index on the table for me as well. So although the user has to
type the date differently now, the tuning adviser said 89% improvement
in speed so they're happier overall with the new solution.

Thanks again,

Alan


  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
[email protected] KB9REU@gmail.com is offline
external usenet poster
 
Posts: 3
Default Mail merge with Word 2003 and SQL 2005 on a date and time field.

Thanks Peter.

I went with your method to return the date but instead of 112, I used
10. I made a view on the SQL server and then had to make some new
Office Data Sources in Word 2003 to connect to it, but that works
perfectly. And I did some SQL profiling and had the server throw in
an index on the table for me as well. So although the user has to
type the date differently now, the tuning adviser said 89% improvement
in speed so they're happier overall with the new solution.

Thanks again,

Alan


  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Mail merge with Word 2003 and SQL 2005 on a date and time field.

Thanks for the feedback - that little speed incentive should come in handy
for others with similar problems!

Peter Jamieson
wrote in message
oups.com...
Thanks Peter.

I went with your method to return the date but instead of 112, I used
10. I made a view on the SQL server and then had to make some new
Office Data Sources in Word 2003 to connect to it, but that works
perfectly. And I did some SQL profiling and had the server throw in
an index on the table for me as well. So although the user has to
type the date differently now, the tuning adviser said 89% improvement
in speed so they're happier overall with the new solution.

Thanks again,

Alan



Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
mail merge from excel-date in excel January 1, 2005 -merged dbase. Elaine Ballon Mailmerge 1 December 23rd 05 09:31 AM
Mail merge from Excel to Word inserts time in a field supposed dismayed microsoft user Mailmerge 1 November 15th 05 08:05 AM
Date Time Field that auto updates - Merge Documents niv Microsoft Word Help 1 October 4th 05 04:34 PM
how do i delete "time" from date/time merge field in mail merge? 3kmom Mailmerge 1 April 7th 05 07:50 AM
How do I filter a Dbase date field in a word mail merge documnet? SMATKINS Mailmerge 0 January 27th 05 05:09 PM


All times are GMT +1. The time now is 11:07 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"