Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-05-2014, 02:50 AM
david_89_ david_89_ is offline insert database as field Windows 7 64bit insert database as field Office 2010 32bit
Novice
insert database as field
 
Join Date: Mar 2014
Posts: 4
david_89_ is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 03-05-2014, 05:49 PM
macropod's Avatar
macropod macropod is offline insert database as field Windows 7 32bit insert database as field Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 03-26-2014, 05:30 AM
david_89_ david_89_ is offline insert database as field Windows 7 64bit insert database as field Office 2010 32bit
Novice
insert database as field
 
Join Date: Mar 2014
Posts: 4
david_89_ is on a distinguished road
Default

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,
Reply With Quote
  #4  
Old 03-26-2014, 06:02 AM
macropod's Avatar
macropod macropod is offline insert database as field Windows 7 32bit insert database as field Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
insert database as field VBA to insert field references SaneMan Word VBA 2 11-01-2013 02:38 AM
insert database as field 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
insert database as field little database gsrikanth Excel 3 06-29-2012 05:23 AM
insert database as field Insert "must complete" text field Markw Word 5 11-19-2009 08:29 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:01 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft