|
Home > Archive > Microsoft SQL Server forum > July 2005 > Sql Problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| I want to loop through a table (tblrelmanager) and merge the results with a
> word doc. The trouble is I am not used to SQL coding and I cant get my
> WHERE
> statement to work.
> Can anyone tell me where I am going wrong
>
> Thanks in advance
> Dave
>
>
>
>
>
> Set rst = db.OpenRecordset("tblrelmanager")
>
> Do Until rst.EOF
>
> Set objWord = GetObject("N:\Customer Management Centre\Warrington\Re
ach
> Team\Database\fire risk assessment.doc", "Word.Document")
> objWord.Application.Visible = True
> objWord.MailMerge.OpenDataSource _
> Name:="D:\Documents and Settings\All Users\Documents\WSS\
WSS.mdb", _
> LinkToSource:=True, _
> Connection:="table tblAccenture", _
> SQLStatement:="SELECT * FROM [tblAccenture] WHERE [Relationship
> Manager] = rst.[Relationship Manager]"
>
> objWord.MailMerge.Execute
> rst.MoveNext
> Loop
| |
| Erland Sommarskog 2005-07-22, 8:23 pm |
| David (david.roebuck@btinternet.com) writes:
> I want to loop through a table (tblrelmanager) and merge the results
> with a word doc. The trouble is I am not used to SQL coding and I cant
> get my WHERE statement to work.
>
> Can anyone tell me where I am going wrong
>
> Set rst = db.OpenRecordset("tblrelmanager")
> Do Until rst.EOF
> Set objWord = GetObject("N:\Customer Management
> Centre\Warrington\Re
ach
> Team\Database\fire risk assessment.doc",
> "Word.Document")
> objWord.Application.Visible = True
> objWord.MailMerge.OpenDataSource _
> Name:="D:\Documents and Settings\All
> Users\Documents\WSS\
WSS.mdb", _
> LinkToSource:=True, _
> Connection:="table tblAccenture", _
> SQLStatement:="SELECT * FROM [tblAccenture] " & _
> WHERE [Relationship Manager] = rst.[Relationship Manager]"
> objWord.MailMerge.Execute
> rst.MoveNext
> Loop
This a newsgroup for MS SQL Server, and I don't know anything about
mail merge. I didn't even know that you used SQL for it.
But I would expect this to be better:
SQLStatement:="SELECT * FROM [tblAccenture] " & _
WHERE [Relationship Manager] = '" & _
rst("Relationship Manager") & "'"
That is, you need to expand the field value before you pass it to
mail merge. And rst is just another VB(A) obejct, and you refer to its
elements and indexes as any other VB(A) object.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
|
|
|
|
|