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?
J2015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

Could you send the mergefield code?

Regards
0
J2015Author Commented:
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\\Documents\\Database.accdb" \c "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\\Users\\Public\\Documents\\Database.accdb;Mode=Read;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" \s "SELECT `ID`, `TimeStamp`, `Employee`, `Type` FROM `qryData` WHERE ((`ID` = '1'))" \h }

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\\Documents\\Database.accdb" \c "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\\Users\\Public\\Documents\\Database.accdb;Mode=Read;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" \s "SELECT `ID`, `TimeStamp`, `Employee`, `Type` FROM `qryData` WHERE ((`ID` = 1))" \h }

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\\Documents\\Database.accdb" \c "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\\Users\\Public\\Documents\\Database.accdb;Mode=Read;Extended Properties=\"\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" \s "SELECT `ID` FROM `qryData` WHERE ((`ID` = 1))" \h }

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?
0
Rgonzo1971Commented:
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))
0
J2015Author Commented:
I abandon the Field Code approach. It's too complicated.
Instead of pulling the data from Word, I'll push it from Access, using VBA.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J2015Author Commented:
Go ahead. Close it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.