#1
|
|||
|
|||
insert database as field
Hi, I have a datasource in an excel file and I am trying to insert a database as a field, where the customer number is equal to a mergefield "customer number" the code is the following: { DATABASE \d "C:\\Statement\\Datasource.xlsx" \s "SELECT `Client Billing Document Number` AS `No Doc`, FORMAT(`Invoice Date`, 'dd/MM/yyyy') AS `Fecha Doc`, FORMAT(`Due Date`, 'dd/MM/yyyy') AS `Vencimiento`, FORMAT(`Client Document Currency Balance`, '##,###,##0.00') AS `Importe`, `Client Document Currency` AS `Moneda` FROM `Data$` WHERE ((`Debtor Number` = '50010117')) ORDER BY `Invoice Date`, `Due Date`" \l "37" \b "51" \h } the mailmerge works, but it gives an error when it tries to open the datasource. Error has occurred: Data type mismatch in criteria expression. Word was unable to open the data source A field calculation error occurred in record 1 Any ideas? Thank you in advance. |
#2
|
||||
|
||||
Try something along the lines of:
{DATABASE \d "C:\\Statement\\Datasource.xlsx" \s "SELECT [Client Billing Document Number] AS [No Doc], FORMAT([Invoice Date], \"dd/MM/yyyy\") AS [Fecha Doc], FORMAT([Due Date], \"dd/MM/yyyy\") AS [Vencimiento], FORMAT([Client Document Currency Balance], \"##,###,##0.00\") AS [Importe], [Client Document Currency] AS [Moneda] FROM [Data$] WHERE [Debtor Number] = \"50010117\" ORDER BY [Invoice Date], [Due Date]" \l "37" \b "51" \h} Depending on how your data are defined, you may also need to change [Data$] to [Data\$].
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Working. Thanks.
However, do you have any idea how can I add an autosum of the field Client Document Currency Balance at the end? also I would like to add conditional formating: If due date < Today () then colour in red Any idea? Thank you. Regards, |
#4
|
||||
|
||||
Sorry, but I've had little exposure to the DATABASE field; most of my mailmerge stuff uses other fields. See, for example, my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://windowssecrets.com/forums/sho...merge-Tutorial or http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it. I can do all manner of formatting with those... For some worked examples, see the attachments to the posts at: https://www.msofficeforums.com/mail-...html#post23345 https://www.msofficeforums.com/mail-...html#post30327 http://windowssecrets.com/forums/sho...l=1#post928391
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA to insert field references | SaneMan | Word VBA | 2 | 11-01-2013 02:38 AM |
Template field to insert picture from default location | LittleCrunch | Word | 1 | 10-28-2013 03:50 AM |
MACRO - Insert row based on Form Field Criteria | Elan05 | Word VBA | 5 | 04-16-2013 06:39 AM |
little database | gsrikanth | Excel | 3 | 06-29-2012 05:23 AM |
Insert "must complete" text field | Markw | Word | 5 | 11-19-2009 08:29 AM |