Mail merge with Word 2003 and SQL 2005 on a date and time field. KB9REU[ at ]gmail.com 6/12/2007 3:37:30 PM 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 [ at ]p1 output,N'SELECT * FROM "Violators" WHERE "Letter 1 Date" = #06/11/2007#',[ at ]p3 output,[ at ]p4 output,[ at ]p5 output select [ at ]p1, [ at ]p3, [ at ]p4, [ at ]p5 Thank you for your help, Alan

Re: Mail merge with Word 2003 and SQL 2005 on a date and time field. "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 6/12/2007 4:38:29 PM 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 <KB9REU[ at ]gmail.com> wrote in message news:1181662650.610141.313580[ at ]i13g2000prf.googlegroups.com... [Quoted Text] > 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 [ at ]p1 output,N'SELECT * FROM "Violators" WHERE > "Letter 1 Date" = #06/11/2007#',[ at ]p3 output,[ at ]p4 output,[ at ]p5 output > select [ at ]p1, [ at ]p3, [ at ]p4, [ at ]p5 > > > Thank you for your help, > Alan >

Re: Mail merge with Word 2003 and SQL 2005 on a date and time field. KB9REU[ at ]gmail.com 6/13/2007 4:29:47 PM 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

Re: Mail merge with Word 2003 and SQL 2005 on a date and time field. KB9REU[ at ]gmail.com 6/13/2007 4:30:00 PM 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

Re: Mail merge with Word 2003 and SQL 2005 on a date and time field. "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 6/13/2007 5:10:52 PM Thanks for the feedback - that little speed incentive should come in handy for others with similar problems! Peter Jamieson <KB9REU[ at ]gmail.com> wrote in message news:1181752187.629619.113560[ at ]g37g2000prf.googlegroups.com... [Quoted Text] > 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 > >