Very helpful pbaldy.
Using the abreviated table names gets the character count down to about 180. More importantly, it means that the query works properly.
Thanks guys - this problem has been bugging me for quite a while!
H
Of course. I'll see what I can do - our database was written externally, so I will need to get full developer rights first.
Hopefully that will do the trick - i'll let you know.
Thanks again.
H
I have a macro that runs from Outlook. That macro looks at the subject line of the current email and compares that to our client list, which is located in our Access database. The macro then opens a Word template and populates various bits of information into the template. The document is...
Here are the debug.prints:
The top one is the combined SQL statement. The lower one is the connection string.
SELECT tbl001_ClientContactStatus.SecondaryMarketSup,tbl001_Contact.EmailAddress1 FROM tbl001_Contact INNER JOIN tbl001_ClientContactStatus ON tbl001_ClientContactStatus.ContactID =...
At the moment it comes straight from the code (will eventually come from somewhere else)
He is the latest version of the code. I have split the SQL into two sections (strSQL and strSQL1) to get under the 255 character limit, but it still doesn't implement both aspects of the WHERE clause.
See...
Sorry - I've lost you. 'Name of the problem query'. I just assigned the query statement to a string (strSQL) in VBA and then passed it to the MailMerge.OpenDataSource method. I don't think it has a name (other that f&*£ing annoying statement that doesn't want to work!).
I also found...
I have tried Yes and -1. Both behave in the same way as True.
The OpenDataSource works fine (no errors), but it just doesn't pick up the last line of the SQL statement.
Interestingly, when you open up the recipients list you can manually filter the entries by "SecondaryMarketSup = TRUE"...
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...
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.
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
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...