CODEWITHSUNDEEP
ms-accessms-wordmailmerge
Robert Simmons
Robert Simmons

Reputation: 1

MS Word Database Field SQL Switch - how to reference merge field?

I'm trying to create a mailmerge that will insert a SQL query into the document. That query will need to reference an Employee ID number. The mergefields are First_Name, Last_Name, and Emp_ID.

The gist is that the merge will be personalized with the name, and then some boilerplate language, and then list all the accounts the person has dealt with.

Here's where I am with the database field code:

DATABASE  \d "M:\\Acme\\Testing Sandbox\\Database11.accdb" \s "SELECT DISTINCT SalesDB.[Parent Acct Name] FROM SalesDB WHERE SalespersonNumber = 9999"

That's functional and great for Employee 9999, but I've got a few hundred of these to create. How can I make SalespersonNumber reference the Emp_ID field I brought in using mail merge? Something like below, but, you know, functional.

DATABASE  \d "M:\\Acme\\Testing Sandbox\\Database11.accdb" \s "SELECT DISTINCT SalesDB.[Parent Acct Name] FROM SalesDB WHERE SalespersonNumber = {Emp_ID}"

Upvotes: 0

Views: 722

Answers (1)

jonsson
jonsson

Reputation: 1301

If the database column's data type is numeric, you need this:

DATABASE  \d "M:\\Acme\\Testing Sandbox\\Database11.accdb" \s "SELECT DISTINCT SalesDB.[Parent Acct Name] FROM SalesDB WHERE SalespersonNumber = {MERGEFIELD Emp_ID}"

If the database column's data type is actually a string type, you need to surround the MERGEFIELD with quotation marks like this:

DATABASE  \d "M:\\Acme\\Testing Sandbox\\Database11.accdb" \s "SELECT DISTINCT SalesDB.[Parent Acct Name] FROM SalesDB WHERE SalespersonNumber = '{MERGEFIELD Emp_ID}'"

Either way, the {MERGEFIELD Emp_ID} code has to be a proper field code, I.e. the {} have to be the field code braces that you can insert using ctrl-F9, not the regular keyboard characters. If you are inserting the DATABASE field using code (e.g. VBA) then you will need to pay attention to that.

Upvotes: 2

Related Questions