Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Database field merge in word
Hi... I have a word document with a database field picking up an excel
spreadsheet. However, where there are blanks in a column of numbers in the excel spreadsheet, the merge picks up a 0 (zero) instead of the required blank. Any ideas on how I can stop this happening? Thanks, Bev |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Database field merge in word
From the Tools menu in Word, select Options and then go to the General tab
and check the box against the "Confirm conversions at open" item. (In Word 2007 it is Office Button Word Options Advanced General Confirm file format conversion on open). Then when you select the data source, you will be given the option of using the DDE method of connection which should read the data as you have it formatted in the table. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Bev wrote: Hi... I have a word document with a database field picking up an excel spreadsheet. However, where there are blanks in a column of numbers in the excel spreadsheet, the merge picks up a 0 (zero) instead of the required blank. Any ideas on how I can stop this happening? Thanks, Bev |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Database field merge in word
Thanks Graham - but I think to do this I would have to start from
scratch - it affects a number of docs each with a number of database fields..... can't seem to apply it retrospectively .. and can't afford to go back and redo them all. I think I must change the excel source file or something. Thanks again though |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Database field merge in word
All you would have to do would be to reattach the data source to the
documents. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Bev" wrote in message ... Thanks Graham - but I think to do this I would have to start from scratch - it affects a number of docs each with a number of database fields..... can't seem to apply it retrospectively .. and can't afford to go back and redo them all. I think I must change the excel source file or something. Thanks again though |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Database field merge in word
When you say "a database field", do you mean
a. a { MERGEFIELD } field that inserts one piece of data during a mailmerge operation or b. a { DATABASE } field that inserts an entire table in one go? -- Peter Jamieson http://tips.pjmsn.me.uk "Bev" wrote in message ... Hi... I have a word document with a database field picking up an excel spreadsheet. However, where there are blanks in a column of numbers in the excel spreadsheet, the merge picks up a 0 (zero) instead of the required blank. Any ideas on how I can stop this happening? Thanks, Bev |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Database field merge in word
Hi there Peter, I mean b. a { DATABASE } field that inserts an
entire table in one go. Dougs method has worked on new doc but I seem to mess up my IF statements when changing the datasource on existing docs as Doug has suggested, but this is more down to my inability than Dougs suggestion I'm sure! Bev |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Database field merge in word
For one reason and another, I have to assume you are using Word XP (2002) or
later. If you were using Word 2000 before, that would at least explain why you are suddenly getting this problem. In Word 2002 and later, you will not be able to get data from an Excel sheet via DDE in a DATABASE field. That probably means that your database field will be using OLE DB to get its data - if you insert a new database field, then use Alt-F9 to show the contents of the database field, it will probably be something like the following: DATABASE \d "the full path name of your xls file with \\ separators" \c "Provider=Microsoft.Jet.OLEDB.4.0;Password=\"\";Us er ID=Admin;Data Source=the full path name of your xls file with \\ separators;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDBatabase Password=\"\";Jet OLEDB:Engine Type=35;Jet OLEDBatabase 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 OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" \s "SELECT * FROM `Sheet1$`" \h There will be some differences if you are using Word 2007. When you get data from Excel via OLE DB, you are at the mercy of the OLE DB provider, which tries to decide what data type is in each column and convert everything else to that type. Although the "rules" for this are not straightforward, blanks are only likely to be returned as 0 if a. Excel has decided that the column they are in is numeric (which probably means that all the first 8 cells in that column are numeric) b. the cell either has at least one blank in it (i.e. is not completely empty) - or perhaps the cell is getting data from elsewhere. Unfortunately, I think your only choices a. are "fix the data" (i.e. if there are cells with " " instead of "", change their contents to "" or "Clear" them) b. modify the SQL in each { DATABASE } field to process any affected columns specially. e.g. instead of SELECT * FROM `Sheet1$` if you have five field f1,f2,f3,f4,f5 and f4 has the problem, try e.g. SELECT f1,f2,f3,iif(trim(cstr(f4)) = '','',f4) as [f4], f5 FROM [Sheet1$] where those '' in the mddle are each two single quotes. Neither of those things is likely to be very practical in the situation you describe, but they are theonly things I can think of, except for the possibility that you do your merges in two steps, i.e. a. select, then copy/paste your Excel data into a Word document b. use that as the data source and even that is likely to cause problems, especially if your sheet has more than 63 columns. -- Peter Jamieson http://tips.pjmsn.me.uk "Bev" wrote in message ... Hi there Peter, I mean b. a { DATABASE } field that inserts an entire table in one go. Dougs method has worked on new doc but I seem to mess up my IF statements when changing the datasource on existing docs as Doug has suggested, but this is more down to my inability than Dougs suggestion I'm sure! Bev |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Database field merge in word
Hi Peter - thanks muchly for your advice. I will have to wait now
until tomorrow when hopefully my brain will be slightly refreshed to try out your suggestion. Thanks for your time. I will feedback my result ... thanks & goodnight! Bev |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
database field - merge single field from excel list | Microsoft Word Help | |||
Database Query on Merge field | Mailmerge | |||
Form Letter Mail Merge using field code: {Database} | Mailmerge | |||
Merge data if database checkbox field is True | Mailmerge | |||
How do I put a picture in a Mail Merge database field. | Mailmerge |