none
Mail Merge String Too Long RRS feed

  • Question

  • I've been able to successfully create Mail Merge Word documents that use MailMerge to query an Access database. However, I just created a new mail merge document that has a problem with the size of the query. I guess there is a character limit. I get the error:

    Run-time error '9105'

    String is longer than 255 characters

    when the code attempts to run the MailMerge. 

    I can't make my query shorter than 255 characters. So now I'm thinking I should create the query in Access and pass the single parameter that it needs. 

    Can anybody give me a quick code sample of doing a mailmerge where instead of querying the database I pass a parameter to the Access query?

    Alternate solutions also appreciated.

    tod

    Monday, May 16, 2016 9:33 PM

Answers

  • I tried using SELECT *, but there are over a hundred fields returned that I have to contend with in setting up the mail merge template. I'm also curious to know why the SQLStatement and SQLStatement1 option does not work. It seems to have been designed specifically for my situation. 

    The fact the query might return 100+ fields is immaterial. If you know you need fields for:

    Case_Number, Plaintiff, Client_First_Name, Client_Last_Name, Court_Level, Court_Number, County, OC_Firm, OC_Fax
    there is no reason you can't add them to the document independently of any query. For example, you could simply configure a document as a mailmerge main document, connect to the data source, insert the required fields from the mailmerge dropdown, then disconnect again. Alternatively, to create the 'Case_Number' mergefield manually, press Ctrl-F9 to create the field braces (i.e. {}) then fill them in thus: {MERGEFIELD Case_Number}.

    As for the query, unless the underscores are in the field names you shouldn't have them in the query. If the field names have spaces, that's what you should use (e.g. Case_Number should be [Case Number] or ‘Case Number‘) Similarly, it appears your 'tblClient cl', 'tblMatter m' and 'tblOC oc' expressions are not properly delimited/formed. Should they be 'tblClient AS cl', 'tblMatter AS m' and 'tblOC AS oc'?

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by todtown Wednesday, May 18, 2016 2:16 AM
    Wednesday, May 18, 2016 1:15 AM

All replies

  • You should split the query into two parts for the .OpenDataSource method:
    • the first part, containing no more than 255 characters, can be assigned to the SQLStatement argument; and
    • the remainder can be assigned to the SQLStatement1 argument.

    For example:
    .OpenDataSource Name:=StrSrc, ReadOnly:=True, AddToRecentFiles:=False, LinkToSource:=False, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
        "Data Source=StrSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM [MyData] WHERE [MyFilter1] IS NOT NULL AND [MyFilter2]='X'", _
        SQLStatement1:="ORDER BY [FieldA] ASC, [FieldB] ASC, [FieldC] ASC", _
        SubType:=wdMergeSubTypeAccess


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Proposed as answer by David_JunFeng Tuesday, May 17, 2016 9:14 AM
    Tuesday, May 17, 2016 2:00 AM
  • >>>I can't make my query shorter than 255 characters. So now I'm thinking I should create the query in Access and pass the single parameter that it needs. 

    Can anybody give me a quick code sample of doing a mailmerge where instead of querying the database I pass a parameter to the Access query?<<<

    According to your description, you could follow macropod's suggestion. In addition the issue with the parameter query is of course that whenever it is accessed, the user is asked for the criteria to be applied.  When mailmerge attempts to obtain data from such a query, there is no opportunity given to the user to provide that query so the attempt to use the data fails.

    For more information, click here to refer about How to automate Word with Visual Basic to create a Mail Merge

    Tuesday, May 17, 2016 3:16 AM
  • I tried it, but I get the error I would normally get when there is a problem with the query. For example, if I don't put single quotes around my variable. The error is that it cannot find my database, but with the extension mdb. (I have an accdb database) I had that problem before and fixing my query solved it. So close..it's agonizing. 

    Here is my code. I just modified the MailMerge.OpenDataSource by putting in the SQLStatement and SQLStatement1. I can put this complete query into Access and it works. 

    'Where dpPath is the path and name of my database and CaseNumber is a string variable that contains a case number.

     objDoc.MailMerge.OpenDataSource Name:=dbPath, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Mode=Read;", _
        SQLStatement:="SELECT Case_Number, Plaintiff, Client_First_Name, Client_Last_Name, Court_Level, Court_Number, County, OC_Firm, OC_Fax ", _
        SQLStatement1:="FROM (tblClient cl INNER JOIN tblMatter m ON cl.ClientID = m.ClientID) INNER JOIN tblOC oc ON m.OCID = oc.OCID WHERE Case_Number='" & CaseNumber & "'"
            
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .ActiveRecord = wdLastRecord
                'Test results of datasource. If there are no records then get out
                If .ActiveRecord = 0 Then
                    MsgBox "There are no records that match your criteria"
                    GoTo MailMergeClose
                End If
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
    MailMergeClose:
        objDoc.MailMerge.DataSource.Close

    Tuesday, May 17, 2016 11:29 PM
  • You previously said you couldn't shorten your query to less than 255 characters. However, from the query string you posted, it would appear that all of:
    Case_Number, Plaintiff, Client_First_Name, Client_Last_Name, Court_Level, Court_Number, County, OC_Firm, OC_Fax
    are field names that aren't necessary for the query as such. Rather, they're just the fields returned by the query that you'll be using in the merge. if so, they could be replaced with * and your query would become:
    "Select * FROM (tblClient cl INNER JOIN tblMatter m ON cl.ClientID = m.ClientID) INNER JOIN tblOC oc ON m.OCID = oc.OCID WHERE Case_Number='" & CaseNumber & "'"

    If your database isn't being found, check what you're passing via dbPath.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, May 18, 2016 12:08 AM
  • I tried using SELECT *, but there are over a hundred fields returned that I have to contend with in setting up the mail merge template. I'm also curious to know why the SQLStatement and SQLStatement1 option does not work. It seems to have been designed specifically for my situation. 

    The path is accurate. What I've discovered through trail and error is that with mail merge, if the query has a syntactical problem and therefore 'appears' to be passing a passing a parameter, I get an error that says that path...databasename.mdb is not found. My database is accdb. The error is misleading. Once the query is correct, the error is solved. 

    I'll keep trying different things to make this work, and post the answer if I find one.

    tod

    Wednesday, May 18, 2016 12:41 AM
  • I tried using SELECT *, but there are over a hundred fields returned that I have to contend with in setting up the mail merge template. I'm also curious to know why the SQLStatement and SQLStatement1 option does not work. It seems to have been designed specifically for my situation. 

    The fact the query might return 100+ fields is immaterial. If you know you need fields for:

    Case_Number, Plaintiff, Client_First_Name, Client_Last_Name, Court_Level, Court_Number, County, OC_Firm, OC_Fax
    there is no reason you can't add them to the document independently of any query. For example, you could simply configure a document as a mailmerge main document, connect to the data source, insert the required fields from the mailmerge dropdown, then disconnect again. Alternatively, to create the 'Case_Number' mergefield manually, press Ctrl-F9 to create the field braces (i.e. {}) then fill them in thus: {MERGEFIELD Case_Number}.

    As for the query, unless the underscores are in the field names you shouldn't have them in the query. If the field names have spaces, that's what you should use (e.g. Case_Number should be [Case Number] or ‘Case Number‘) Similarly, it appears your 'tblClient cl', 'tblMatter m' and 'tblOC oc' expressions are not properly delimited/formed. Should they be 'tblClient AS cl', 'tblMatter AS m' and 'tblOC AS oc'?

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by todtown Wednesday, May 18, 2016 2:16 AM
    Wednesday, May 18, 2016 1:15 AM
  • I took your advice and shortened my query by replacin all of the field names with *. You're correct in that no matter how much data is returned, the letter will still only use the ones it needs. It seems like a wasteful solution, and I'll still try to figure out why my SQLStatement and SQLStatement1 option did not work, but I gotta move on to the next thing. 

    Thanx all.

    tod

    Wednesday, May 18, 2016 2:20 AM