J2015
asked on
Invalid Merge Fields error in Word 2010
I've inserted a "DATABASE" field code in a Word document to display the results of a query, then I toggled the field codes and edited the SQL query so that less database columns are selected. I then updated the field codes and, as expected, only the selected database columns showed up in the results table. A few hours later, I repeated the same procedure on a new file but now every time I update the field codes I'm getting an "Invalid Merge Field" error popup.
Why is it suddenly doing this? How can I prevent it?
Why is it suddenly doing this? How can I prevent it?
ASKER
A) Following is the string I obtain immediately after insertion of a DATABASE field code. Notice the first bug here: Word encloses the value of the SQL "WHERE" condition (`ID` = '1') between apostrophes ('1') even if the column data type (ID) is numeric. On the first attempt the field code fails with the following 3 error messages: 1) "Error has occured: Data type mismatch in criteria expression", 2) "Word was unable to open the data source", and 3) "Word could not replace the selection with the specified database". The document displays the following text at the insertion point of the field code: "Error! Cannot open data source.". Here is the field code string:
{ DATABASE \d "C:\\Users\\Public\\Docume
B) I then edit the SQL query to remove the apostrophes surrounding the "1" in "WHERE ((`ID` = '1'))". This is the new field code string:
{ DATABASE \d "C:\\Users\\Public\\Docume
C) I update the field and, as expected, I obtain a results table in my Word document showing three columns, a header row containing the column names, and one row of data.
D) Then, I want to modify the SQL query to display a results table showing only the ID column. This is my field code string after the edit:
{ DATABASE \d "C:\\Users\\Public\\Docume
E) I update the field code again, and then I start getting the "Invalid Merge Field" popup, asking me to remove the missing TimeStamp, Employee, and Type columns.
F) This only happens when columns other than the ID are removed from the SELECT. If I edit the SELECT to remove the ID only, or the ID and the previous columns, or the ID and any other column(s), I don't get this behaviour. As long as the ID is not part of the SELECT, there is no problem. Is it because the ID is the primary key, or is it because it is part of the WHERE clause? If I insert another similar field code in my document, but this time use a different column in it's WHERE clause (preferably of a "String" type this time), I don't get any of the above problems, no matter what field(s) I edit out of the SQL string. However, now no matter what column(s) I edit out, I always get a table containing all the original columns! And there we go. A whole new set of obstacles. Strange, to say the least. Of course, the reason why I want to be able to control the SQL this way is to prevent me from having to connect to a specialized query for every different field code in my document. Why am I using field codes instead of Mail Merge you may ask? Glad you asked. It's because my Word document contains 1-to-1 as well as 1-to-many elements from my data source. Sometimes I want to show pieces of information that are related to the ID in a 1-1 relationship, and in other places in the document I want to display rows of information related in a 1-many relationship to the ID. The Mail Merge does not seem to offer this capability. This is why I am using the DATABASE field code method, and use it for every merge location in my document.
G) Could all this be better controled using VBA? How?
{ DATABASE \d "C:\\Users\\Public\\Docume
B) I then edit the SQL query to remove the apostrophes surrounding the "1" in "WHERE ((`ID` = '1'))". This is the new field code string:
{ DATABASE \d "C:\\Users\\Public\\Docume
C) I update the field and, as expected, I obtain a results table in my Word document showing three columns, a header row containing the column names, and one row of data.
D) Then, I want to modify the SQL query to display a results table showing only the ID column. This is my field code string after the edit:
{ DATABASE \d "C:\\Users\\Public\\Docume
E) I update the field code again, and then I start getting the "Invalid Merge Field" popup, asking me to remove the missing TimeStamp, Employee, and Type columns.
F) This only happens when columns other than the ID are removed from the SELECT. If I edit the SELECT to remove the ID only, or the ID and the previous columns, or the ID and any other column(s), I don't get this behaviour. As long as the ID is not part of the SELECT, there is no problem. Is it because the ID is the primary key, or is it because it is part of the WHERE clause? If I insert another similar field code in my document, but this time use a different column in it's WHERE clause (preferably of a "String" type this time), I don't get any of the above problems, no matter what field(s) I edit out of the SQL string. However, now no matter what column(s) I edit out, I always get a table containing all the original columns! And there we go. A whole new set of obstacles. Strange, to say the least. Of course, the reason why I want to be able to control the SQL this way is to prevent me from having to connect to a specialized query for every different field code in my document. Why am I using field codes instead of Mail Merge you may ask? Glad you asked. It's because my Word document contains 1-to-1 as well as 1-to-many elements from my data source. Sometimes I want to show pieces of information that are related to the ID in a 1-1 relationship, and in other places in the document I want to display rows of information related in a 1-many relationship to the ID. The Mail Merge does not seem to offer this capability. This is why I am using the DATABASE field code method, and use it for every merge location in my document.
G) Could all this be better controled using VBA? How?
pls try
SELECT a.`ID` FROM `qryData` As a WHERE ((a.`ID` = 1))
or maybe
SELECT a.`ID` FROM `qryData` a WHERE ((a.`ID` = 1))
SELECT a.`ID` FROM `qryData` As a WHERE ((a.`ID` = 1))
or maybe
SELECT a.`ID` FROM `qryData` a WHERE ((a.`ID` = 1))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Go ahead. Close it.
Could you send the mergefield code?
Regards