Merge not wking from coding but does from Word doc

I have a mail merge doc using an access table as the datasource. In the tbl
for the datasource there are some yes/no fields. In my merge doc, I have some
if then else set up for these. When I am in the doc and click the merge all
works fine. But when I run the code in the database my if then else don't
work. It is the same info in the table both times. This is example of my if
then else in merge doc:
{IF{MERGEFIELD LegalAgeVio} = 0 "____""__X__"}
Basically if the field is no it will put a blank line, else it will mark the
line with an X.
When I merge from doc, these all are correct, but from the database, it
marks all of the with an X whether the statement is true or false. I have
copied my coding below. Any help would greatly be appreciated. I am at a
loss. I have done many of these and this is the first time this has been an
issue:

Private Sub cmdWarningLetter_Click()
Dim objWord As Word.Document

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From tblWarningLetter"
DoCmd.OpenQuery "qryWarningLetter"

Set objWord = GetObject("G:\Users\AmyC\child
labor\WordDocuments\WarningLetter.doc", "Word.Document")

objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource Name:="G:\Users\AmyC\child
labor\ChildLabor.mdb", LinkToSource:=True, Connection:="DSN=MS Access
Database;DBQ=G:\Users\AmyC\child labor\ChildLabor.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5" ' SQLStatement:="SELECT *
From[tblWarningLetter]"
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
objWord.ActiveWindow.WindowState = wdWindowStateMaximize
objWord.Close (wdDoNotSaveChanges)
Set objWord = Nothing

DoCmd.SetWarnings True

End Sub

Have you tried updating the fields in the new document (Ctrl+A, then F9)

I would apply the condition by designing a Query in the Access database
using an IF() construction that output the required string depending on the
value of the field.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"AJ" wrote in message
...
I have a mail merge doc using an access table as the datasource. In the
tbl
for the datasource there are some yes/no fields. In my merge doc, I have
some
if then else set up for these. When I am in the doc and click the merge
all
works fine. But when I run the code in the database my if then else don't
work. It is the same info in the table both times. This is example of my
if
then else in merge doc:
{IF{MERGEFIELD LegalAgeVio} = 0 "____""__X__"}
Basically if the field is no it will put a blank line, else it will mark
the
line with an X.
When I merge from doc, these all are correct, but from the database, it
marks all of the with an X whether the statement is true or false. I have
copied my coding below. Any help would greatly be appreciated. I am at a
loss. I have done many of these and this is the first time this has been
an
issue:

Private Sub cmdWarningLetter_Click()
Dim objWord As Word.Document

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From tblWarningLetter"
DoCmd.OpenQuery "qryWarningLetter"

Set objWord = GetObject("G:\Users\AmyC\child
labor\WordDocuments\WarningLetter.doc", "Word.Document")

objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource Name:="G:\Users\AmyC\child
labor\ChildLabor.mdb", LinkToSource:=True, Connection:="DSN=MS Access
Database;DBQ=G:\Users\AmyC\child labor\ChildLabor.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5" ' SQLStatement:="SELECT *
From[tblWarningLetter]"
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
objWord.ActiveWindow.WindowState = wdWindowStateMaximize
objWord.Close (wdDoNotSaveChanges)
Set objWord = Nothing

DoCmd.SetWarnings True

End Sub

I think one of the following nested fields will work, but you had better
check for yourself.

{ IF { =IF({ MERGEFIELD LegalAgeVio },1,0) } = 1 "____""__X__"}

or

{ =IF({ MERGEFIELD LegalAgeVio },1,-1) \#"'____';'__X__'" }


(i.e. every pair of {} has to be a pair of the special field code braces
that you can enter using ctrl-F9)

Background: the value that Word receives from a
boolean/Yes/No/True/False field in Access depends primarily on the
method you use to connect to Access - AFAIK they are as follows:

Connection method "Yes" "No"
DDE -1 0
ODBC 1 0
OLE DB True False

If you are using Word 2000 or earlier, by default Word will connect
using DDE. If you are using Word 2002 or later, by default Word will
connect using OLE DB. The Connection parameter in your code specifies an
ODBC conection - so whichever version of Word you are using, the results
will be different from a manual connection using the default method. So
you can either
a. create a query that always returns a True/False value in the same
way, and use that as the data source, (as Doug suggests) or
b. ensure that your VBA connection uses the same method as the manual
one. If it's Word 2002 or later, you can /probably/ do that by leaving
the Connection paramete rout altogether. Or
c. Create an { IF } field that will work with any of the above values.
Unfortunately, when the value is True or False, it never = 0, so you
always get the "false" result. The { =IF } construct correctly
interprets True/False/-1/0 AFAIK, but unfortunately you cannot use

{ =IF({ MERGEFIELD LegalAgeVio },"____","__X__") }

because the =IF cannot have strings in its result values.

Peter Jamieson

http://tips.pjmsn.me.uk

On 20/01/2010 17:33, AJ wrote:
I have a mail merge doc using an access table as the datasource. In the tbl
for the datasource there are some yes/no fields. In my merge doc, I have some
if then else set up for these. When I am in the doc and click the merge all
works fine. But when I run the code in the database my if then else don't
work. It is the same info in the table both times. This is example of my if
then else in merge doc:
{IF{MERGEFIELD LegalAgeVio} = 0 "____""__X__"}
Basically if the field is no it will put a blank line, else it will mark the
line with an X.
When I merge from doc, these all are correct, but from the database, it
marks all of the with an X whether the statement is true or false. I have
copied my coding below. Any help would greatly be appreciated. I am at a
loss. I have done many of these and this is the first time this has been an
issue:

Private Sub cmdWarningLetter_Click()
Dim objWord As Word.Document

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From tblWarningLetter"
DoCmd.OpenQuery "qryWarningLetter"

Set objWord = GetObject("G:\Users\AmyC\child
labor\WordDocuments\WarningLetter.doc", "Word.Document")

objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource Name:="G:\Users\AmyC\child
labor\ChildLabor.mdb", LinkToSource:=True, Connection:="DSN=MS Access
Database;DBQ=G:\Users\AmyC\child labor\ChildLabor.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5" ' SQLStatement:="SELECT *
From[tblWarningLetter]"
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
objWord.ActiveWindow.WindowState = wdWindowStateMaximize
objWord.Close (wdDoNotSaveChanges)
Set objWord = Nothing

DoCmd.SetWarnings True

End Sub

Mr. Robbins,
I had to end up changing the query in Access. For some reason it would not
accept 0 and -1 from the database, but just merging from word it would. I had
the query update the fields prior to the merge to string yes and no. After
that it worked great. Don't know if it was an issue with the versions I am
using or what. But thank you for your time.
Thanks,
Amy

"Doug Robbins - Word MVP" wrote:

Have you tried updating the fields in the new document (Ctrl+A, then F9)

I would apply the condition by designing a Query in the Access database
using an IF() construction that output the required string depending on the
value of the field.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"AJ" wrote in message
...
I have a mail merge doc using an access table as the datasource. In the
tbl
for the datasource there are some yes/no fields. In my merge doc, I have
some
if then else set up for these. When I am in the doc and click the merge
all
works fine. But when I run the code in the database my if then else don't
work. It is the same info in the table both times. This is example of my
if
then else in merge doc:
{IF{MERGEFIELD LegalAgeVio} = 0 "____""__X__"}
Basically if the field is no it will put a blank line, else it will mark
the
line with an X.
When I merge from doc, these all are correct, but from the database, it
marks all of the with an X whether the statement is true or false. I have
copied my coding below. Any help would greatly be appreciated. I am at a
loss. I have done many of these and this is the first time this has been
an
issue:

Private Sub cmdWarningLetter_Click()
Dim objWord As Word.Document

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From tblWarningLetter"
DoCmd.OpenQuery "qryWarningLetter"

Set objWord = GetObject("G:\Users\AmyC\child
labor\WordDocuments\WarningLetter.doc", "Word.Document")

objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource Name:="G:\Users\AmyC\child
labor\ChildLabor.mdb", LinkToSource:=True, Connection:="DSN=MS Access
Database;DBQ=G:\Users\AmyC\child labor\ChildLabor.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5" ' SQLStatement:="SELECT *
From[tblWarningLetter]"
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
objWord.ActiveWindow.WindowState = wdWindowStateMaximize
objWord.Close (wdDoNotSaveChanges)
Set objWord = Nothing

DoCmd.SetWarnings True

End Sub

.