Field code SQL syntax

Andrew 0 Reputation points
2023-04-28T16:24:18.62+00:00

Hi all,

I want to use Word mail merge to generate dynamic order summary sheets that list every line item on that order. I have a Word file linked to an Excel file that pulls order data through a database field. Some orders might only have 1 line item while others have dozens. The Excel table goes by line items, so there will be multiple rows corresponding to the same order. So far, my fields look like this:

Summary sheet for order# { MERGEFIELD OrderNumber }

{ DATABASE  \d "(database instructions)" \s "SELECT `OrderNumber`, `Data1`, `Data2`, `Data3` FROM `SourceTable$`" }

But that loads the entire table onto each page. I figured I could modify the SQL statement with a WHERE clause to only select data for the corresponding OrderNumber for that specific summary sheet.

Summary sheet for order# { MERGEFIELD OrderNumber }

{ DATABASE  \d "(database instructions)" \s "SELECT `OrderNumber`, `Data1`, `Data2`, `Data3` FROM `SourceTable$` WHERE `OrderNumber` = { MERGEFIELD OrderNumber }" }

But no matter how I try it, I always get: Error! Cannot open data source. Am I using the correct syntax? Is it something with data types?

Thank you in advance for any guidance

Word
Word
A family of Microsoft word processing software products for creating web, email, and print documents.
714 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,604 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,067 questions
{count} votes