SQL query issue (MailMerge.OpenDataSource)

[hazz_London]

Hi there.

I'm probably missing something really obvious, but I can't seem to get this query to work.

I'm building a VBA module in Word that pulls data from an Access database in order to automate a mail merge (the email addresses to be used are driven by the query). The code works fine, expect for one line of the query - !tbl001_ClientContactStatus.SecondaryMarketSup = True".

I have used this exact same query in Outlook to open a recordset and then pull off the list of email address and it worked fine. So I was surprised to find that it didn't work in the Word VBA environment.

If I take out the dodgy line (so there is just a WHERE on tbl001_ClientContactStatus.CompanyID = ...) it works fine. I have tried many variations, including just one WHERE on tbl001_ClientContactStatus.SecondaryMarketSup = True, with quotation marks etc. but no joy.

Could it be down to the order? Any help would be hugely appreciated.

I have included below an example debug.print of the SQL statement, as well as the VBA code that builds it.

Thanks again for any help.


[Debug.Print]

SELECT tbl001_ClientContactStatus.SecondaryMarketSup,tbl001_Contact.EmailAddress1 FROM tbl001_Contact INNER JOIN tbl001_ClientContactStatus ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID WHERE tbl001_ClientContactStatus.CompanyID =47AND tbl001_ClientContactStatus.SecondaryMarketSup = True;


[VBA code]

Dim wdApp As Word.Application
Dim wdDoc As Word.Document

Dim strSQL As String
Dim MyClientID As String

Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument


MyClientID = 47

strSQL = "SELECT tbl001_ClientContactStatus.SecondaryMarketSup,tbl001_Contact.EmailAddress1 "
strSQL = strSQL & "FROM tbl001_Contact "
strSQL = strSQL & "INNER JOIN tbl001_ClientContactStatus "
strSQL = strSQL & "ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID "
strSQL = strSQL & "WHERE tbl001_ClientContactStatus.CompanyID =" & MyClientID
strSQL = strSQL & "AND tbl001_ClientContactStatus.SecondaryMarketSup = True"
strSQL = strSQL & ";"

Debug.Print strSQL

wdDoc.MailMerge.OpenDataSource _
Name:="Y:\MAINFOLDER\SH_Management_BE.mdb", _
LinkToSource:=True, AddToRecentFiles:=False, _
Connection:="Data", _
SQLStatement:=strSQL

wdDoc.MailMerge.Destination = wdSendToEmail
'wdDoc.MailMerge.Execute

Set wdApp = Nothing
Set wdDoc = Nothing

End Sub

 

[pbaldy]

Note the lack of a space between the value 47 and the word "AND".

 

[hazz_London]

Thanks. I spotted that earlier and have tried it with a space after the 47. That doesn't work either.

When I say ' doesn't work' a "select table" windows open, which I presume means the SQL statement isn't valid (http://support.microsoft.com/kb/289830).

Here is the modified SQL statement, with a space after 47, which doesn't work.

SELECT tbl001_ClientContactStatus.SecondaryMarketSup,tbl001_Contact.EmailAddress1 FROM tbl001_Contact INNER JOIN tbl001_ClientContactStatus ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID WHERE tbl001_ClientContactStatus.CompanyID =47 AND tbl001_ClientContactStatus.SecondaryMarketSup = True;

If you take out the last bit, it works fine:

SELECT tbl001_ClientContactStatus.SecondaryMarketSup,tbl001_Contact.EmailAddress1 FROM tbl001_Contact INNER JOIN tbl001_ClientContactStatus ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID WHERE tbl001_ClientContactStatus.CompanyID =47;

 

[hazz_London]

For reference, the tbl001_ClientContactStatus.SecondaryMarketSup field is set to a Yes/No data type.

"Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off)."

Hence we I would have thought that the constant 'True' could have been picked up in SQL query.

 

[pbaldy]

I don't see anything wrong with that. You should be able to use True, but perhaps not from Word (I have no experience with automating Word). Try using -1 instead, which is how Access actually stores a Yes/No value (-1 for True, 0 for False).

 

[hazz_London]

Thanks again. I have tried using -1 and 0, but with no joy.

This must be an issue / bug specific to Word, as the query works fine when used in Outlook to obtain data from the Access database.

I may have to find a Word-specific VBA forum to find an answer to this...

 

[pbaldy]

Sorry I was unable to help. I'll see if anyone has some Word experience.

 

[boblarson]

I've run into the problem before of using True. Try using <> 0 instead.

 

[hazz_London]

No joy again! Can't get my head around why it doesn't like that line. I've checked and rechecked the table and field names (as I mentioned before, this query has come from another bit of Outlook VBA code which works fine!). Oh well, a bit of mystery...

Here is what I tried:

SELECT tbl001_ClientContactStatus.SecondaryMarketSup,tbl001_Contact.EmailAddress1 FROM tbl001_Contact INNER JOIN tbl001_ClientContactStatus ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID WHERE tbl001_ClientContactStatus.CompanyID = 47 AND tbl001_ClientContactStatus.SecondaryMarketSup <> 0;

I even tried passing in the 'true' value into the SQL statement via a boolean variable, but that doesn't work either.

I might have to rethink my approach, but it's one of those problems that you want to get to the bottom of.

H

 

[hazz_London]

This is nuts. I took out the 'tbl001_ClientContactStatus.SecondaryMarketSup' bit from the SELECT section and the 'true' bit worked.

However, it only works if there is one WHERE clause. Maybe the mail merge query function only allows one WHERE clause?

If so, might there be a workaround (I read something about sub-queries a while ago)?


Here is the query that worked:

SELECT tbl001_Contact.EmailAddress1 FROM tbl001_Contact INNER JOIN tbl001_ClientContactStatus ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID WHERE tbl001_ClientContactStatus.SecondaryMarketSup = True;

Whereas this didn't:

SELECT tbl001_Contact.EmailAddress1 FROM tbl001_Contact INNER JOIN tbl001_ClientContactStatus ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID WHERE tbl001_ClientContactStatus.SecondaryMarketSup = True AND tbl001_ClientContactStatus.CompanyID =47;

 

[boblarson]

I don't know if you noticed it but Paul mentioned this a while ago. Your

AND tbl001_ClientContactStatus.CompanyID =47;

has the 47 too close to the = sign. I think it is having a problem because of it. You need to make sure the output has it:

AND tbl001_ClientContactStatus.CompanyID = 47;

 

[hazz_London]

Sorry about the long radio silence. Out SBS server died so I have been pre-occupied!

I've tried it with a space before the '47' and it still doesn't work (see sql statement below). Very strange. I'm going to have another play with it (with a fresh mind) - i'll keep you posted.

SELECT tbl001_ClientContactStatus.SecondaryMarketSup,tbl0 01_Contact.EmailAddress1 FROM tbl001_Contact INNER JOIN tbl001_ClientContactStatus ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID WHERE tbl001_ClientContactStatus.CompanyID = 47 AND tbl001_ClientContactStatus.SecondaryMarketSup = True;

H

 

[vbaInet]

I can't imagine what could be the problem there but I reckon if you create another query based on the problem query it will work.

 

[hazz_London]

That's what I thought. It might be something to do with the way the MailMerge.OpenDataSource method is set up. It seems there might be an issue with the 'connection' bit.

It just seems strange that it works with a more simply query, but not one with 2 WHERE clauses. Perhaps the way I was using the OpenDataSource method only supports 'simple' SQL statements, so i'm going to try another version.

Thanks as always - at least I know the SQL bit is right!

H

 

[vbaInet]

It should work with that sql construct. It's simple enough.

By the way, is the source actually a query or a table? If it's a query, does it have a subquery or any complex calculation in it? Also, what version of Word are you using?

 

[hazz_London]

It's Word 2003 (and Access 2003).

All the sources in the SQL statement refer to tables (hence tbl001 etc).

As a potential work around I might try to use good old MSQuery to set up the query and then use that to drive OpenDataSource method. I just have to work out how to do that!

H

 

[hazz_London]

Actually - using MSQuery is no good because the ClientID will be change. I would have to use VBA to open MSQuery to build and save the query with the right ClientID.

Sounds a bit messy.

 

[vbaInet]

Try a make table query then use that as the source of your mailmerge.

 

[hazz_London]

Ok - just spotted something.

There was an unnecessary space in the one of the 'tbl001' bits in the SELECT statement. ARG! Can't believe I didn't spot it.

I have slightly changed the .OpenDataSource bit, but I don't think that was the cause of the issue.

Even more annoyingly, this SQL still doesn't work as I would like it to (I kind of back to square one). Despite the second WHERE clause in the SQL, the recipient list that is generated for the mail merge includes those entries where "tbl001_ClientContactStatus.SecondaryMarketSup" is either TRUE or FALSE. i.e. it doesn't filter out the FALSE's.

The code below shows the bit that was wrong and is now fixed.

H

Dim wdApp As Word.Application
Dim wdDoc As Word.Document

Dim strSQL As String
Dim MyClientID As String
Dim strConnection As String

Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument


MyClientID = 47

strSQL = "SELECT tbl001_ClientContactStatus.SecondaryMarketSup,[COLOR="Red"]tbl001[/COLOR]_Contact.EmailAddress1 "
strSQL = strSQL & "FROM tbl001_Contact "
strSQL = strSQL & "INNER JOIN tbl001_ClientContactStatus "
strSQL = strSQL & "ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID "
strSQL = strSQL & "WHERE tbl001_ClientContactStatus.CompanyID = " & MyClientID
strSQL = strSQL & " AND tbl001_ClientContactStatus.SecondaryMarketSup = True"
strSQL = strSQL & ";"

Debug.Print strSQL

strConnection = "DSN=MS Access Databases;" _
        & "DBQ=Z:\PROBASE\ADMIN\SH_Management_BE.mdb;" _
        & "FIL=RedISAM;"

Debug.Print strConnection

With wdDoc.MailMerge

.OpenDataSource Name:="Z:\PROBASE\ADMIN\SH_Management_BE.mdb", _
Connection:=strConnection, _
SQLStatement:=strSQL, _
SubType:=MergeSubTypeWord2000

End With

wdDoc.MailMerge.Destination = wdSendToEmail
'wdDoc.MailMerge.Execute

Set wdApp = Nothing
Set wdDoc = Nothing

End Sub

 

[vbaInet]

Maybe use Yes instead of True. Or look in the table and if it uses -1 and 0, use -1 for True.