Replace() inside a query for MSWord OpenDatasource using mailmerge with VBA

Question

I have a field in a database, ending with :. My idea was to use { INCLUDEPICTURE "pathname{MERGEFIELD f1}.jpg", having stored previously the images' names in a folder with the names of the field values. However, I cannot name a file with a : character in it, and I thought of using the replace function inside the query that is inside the OpenDatasource function in ms word, but it does not work apparently.

Here goes the code:

Private Sub Document_Open()
    Dim strConnection As String
    With Me.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource _
           Name:="E:\jobDB.mdb", _
           LinkToSource:=True, AddToRecentFiles:=False, ConfirmConversions:=True, _
           Connection:="TABLE t1", SQLStatement:="SELECT t1.name, replace(t1.f1, """:""", """") as repFld FROM t1;"
    End With
End Sub

Private Sub Document_Close()
   If Me.MailMerge.State = wdMainAndDataSource Then _
      Me.MailMerge.MainDocumentType = wdNotAMergeDocument
End Sub

Doing so, I was hoping to remove the : and make INCLUDETEXT work, but this does not work and when the code is executed, a dialog box is shown for me to select the table of the database.

I don't know if theres is something wrong, or if there is another way of achieving this, or if there is any field or tag or flag to replace characters inside a mergefield.

I wanted to use VBA only for connecting to the datasource andbut I wanted to use mailmerge for this since I researched how it works and I could do what i wanted with conditional fields, otherwise I will have to use VBA and formfields.

Answer 1

There was nothing wrong with your general approach. It's just that Word is very particular about the syntax you use in an OpenDataSource query, and often does not report connection errors, in some cases just retrieving the data that the SQL SELECT * FROM [some table name] would retrieve.

In this case, the chances are that the only problems are that you needed "":"" rather than """:""", to surround the field alias name repFld with straight quote ' characters, and to surround the table name with [], like this:

.OpenDataSource _
  Name:="E:\jobDB.mdb", _
  SQLStatement:="SELECT t1.name, replace(t1.f1, "":"", """") as 'repFld' FROM [t1]"

You shouldn't actually need any of the other parameters in the call.

Personally I prefer to use single quotation marks inside Jet/Access SQL, like this, as it works and differentiates more clearly between the SQL code and the Word VBA code. But you don't have to:

.OpenDataSource _
  Name:="E:\jobDB.mdb", _
  SQLStatement:="SELECT t1.name, replace(t1.f1, ':', '') as repFld FROM [t1]"

ISTR that the replace function did not always work when connecting from Word to Access, because some Access VBA functions were missing from an internal table that the OLE DB provider relied on. If replace() doesn't work and you always have one and only one : character at the end of the field data, you should really be able to use

.OpenDataSource _
  Name:="E:\jobDB.mdb", _
  SQLStatement:="SELECT t1.name, left(t1.f1, len(t1.f1) - 1) as 'repFld' FROM [t1]"

(That works with fields with no text in them, too, but if some fields don't have a terminating colon you'd probably need to add some complication).