Merging Access data into a Word document Danny 7/6/2006 12:09:01 AM Is it possible to take the data from Access, and merge it into just one Word document? I wish to create a form letter addressed to one person, using the data that I have stored in an Access database. However, the only options I have discovered so far are to either create a mass-mailing, or to create a poorly formatted report in Access, then use the "publish it" function. This outputs again a poorly formatted document. Is it possible for me to design a form letter in Word, and have it pull the data for just one letter, rather than 100?

Re: Merging Access data into a Word document "Doug Robbins - Word MVP" <dkr[ at ]REMOVECAPSmvps.org> 7/6/2006 3:08:44 AM See the "Super Easy Word Merge" item at http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html -- 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 "Danny" <Danny[ at ]discussions.microsoft.com> wrote in message news:46C50DDB-25A8-4F2C-B461-160DDA5DEC7B[ at ]microsoft.com... [Quoted Text] > Is it possible to take the data from Access, and merge it into just one > Word > document? I wish to create a form letter addressed to one person, using > the > data that I have stored in an Access database. However, the only options I > have discovered so far are to either create a mass-mailing, or to create a > poorly formatted report in Access, then use the "publish it" function. > This > outputs again a poorly formatted document. > > Is it possible for me to design a form letter in Word, and have it pull > the > data for just one letter, rather than 100?

Re: Merging Access data into a Word document cjg.groups[ at ]gmail.com 7/6/2006 3:23:41 AM The solution depends on what level of automation that you need. In Word 2003, you can use Tools > Letters/Mailings > Mail Merge, and after selecting the Access table in step 3, check only the one recipient that you want. You may also try Albert Kallal's "MergeSingleWord". http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html I automated the process with Visual Basic code to merge the current record being displayed on an Access form. The form has fields to find and identify the desired record, a command button to launch this code, and a text box bound the data table primary key called OrderID. This article was used as a foundation: http://support.microsoft.com/?id=209976 The code requires reference (Tools > References) to Word 11 Object Library; may need to browse for C:\PF\MS Office\Office11\msword.olb The Word template should not be bound to a data source. Bind it to a data source only to easily enter the merge fields, then convert it back to a normal Word document (Word 2000: Mail Merge Helper > 1 > Restore to normal Word document. Word 2003: Mail merge toolbar > Main document setup > Normal Word document). You may get errors from Word such as the "Data Link" window or complaints that Access is locked and needs an admin password. Post them to this thread. Here is the code. Any MVPs and pros, feel free to correct. Sorry for line wrapping; not sure why it does that. Function OutputDoc() Dim appWord As Word.Application Dim objWord As Word.Document Set appWord = New Word.Application Set objWord = appWord.Documents.Open("C:\MergeTemplate.doc") ' Make Word visible. appWord.Visible = True ' Set the mail merge data source as the database. Note the query parameter. objWord.MailMerge.OpenDataSource Name:="Database.mdb", LinkToSource:=True, Connection:="TABLE tblData", SQLStatement:="SELECT * FROM [tblData] WHERE [Orders.OrderID] = " & Forms!OrderForm!OrderID ' Execute the mail merge. objWord.MailMerge.Destination = wdSendToNewDocument objWord.MailMerge.Execute ' clean up and close Word, closing windows in reverse order that they were opened Set objWord = appWord.Documents("Form Letters1") objWord.Close wdDoNotSaveChanges Set objWord = appWord.Documents("Sampling Request Form - merge.doc") objWord.SaveAs ("C:\Output.doc") appWord.Quit Set appWord = Nothing End Function Good luck. Danny wrote: [Quoted Text] > Is it possible to take the data from Access, and merge it into just one Word > document? I wish to create a form letter addressed to one person, using the > data that I have stored in an Access database. However, the only options I > have discovered so far are to either create a mass-mailing, or to create a > poorly formatted report in Access, then use the "publish it" function. This > outputs again a poorly formatted document. > > Is it possible for me to design a form letter in Word, and have it pull the > data for just one letter, rather than 100?

Re: Merging Access data into a Word document Access101 7/6/2006 4:18:02 PM You guys look like you know what you're talking about - slick tools. Maybe you know this one too: In my Main Merge doc I have: <<fname>> <<fname>> In the Data doc I have column heading and two names fname Bill Mike When I merge, Bill, of course, replaces each <<fname>> with Bill and looks like this: Bill Bill What I want, is this: Bill Mike Is this possible? Any help is appreciated. "cjg.groups[ at ]gmail.com" wrote: [Quoted Text] > The solution depends on what level of automation that you need. > > In Word 2003, you can use Tools > Letters/Mailings > Mail Merge, and > after selecting the Access table in step 3, check only the one > recipient that you want. > > You may also try Albert Kallal's "MergeSingleWord". > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html > > I automated the process with Visual Basic code to merge the current > record being displayed on an Access form. The form has fields to find > and identify the desired record, a command button to launch this code, > and a text box bound the data table primary key called OrderID. > > This article was used as a foundation: > http://support.microsoft.com/?id=209976 > The code requires reference (Tools > References) to Word 11 Object > Library; may need to browse for C:\PF\MS Office\Office11\msword.olb > > The Word template should not be bound to a data source. Bind it to a > data source only to easily enter the merge fields, then convert it back > to a normal Word document (Word 2000: Mail Merge Helper > 1 > Restore > to normal Word document. Word 2003: Mail merge toolbar > Main document > setup > Normal Word document). > > You may get errors from Word such as the "Data Link" window or > complaints that Access is locked and needs an admin password. Post > them to this thread. > > Here is the code. Any MVPs and pros, feel free to correct. Sorry for > line wrapping; not sure why it does that. > > > Function OutputDoc() > > Dim appWord As Word.Application > Dim objWord As Word.Document > > Set appWord = New Word.Application > Set objWord = appWord.Documents.Open("C:\MergeTemplate.doc") > > ' Make Word visible. > appWord.Visible = True > ' Set the mail merge data source as the database. Note the query > parameter. > objWord.MailMerge.OpenDataSource Name:="Database.mdb", > LinkToSource:=True, Connection:="TABLE tblData", SQLStatement:="SELECT > * FROM [tblData] WHERE [Orders.OrderID] = " & Forms!OrderForm!OrderID > ' Execute the mail merge. > objWord.MailMerge.Destination = wdSendToNewDocument > objWord.MailMerge.Execute > > ' clean up and close Word, closing windows in reverse order that > they were opened > Set objWord = appWord.Documents("Form Letters1") > objWord.Close wdDoNotSaveChanges > Set objWord = appWord.Documents("Sampling Request Form - merge.doc") > objWord.SaveAs ("C:\Output.doc") > appWord.Quit > Set appWord = Nothing > End Function > > > Good luck. > > > Danny wrote: > > Is it possible to take the data from Access, and merge it into just one Word > > document? I wish to create a form letter addressed to one person, using the > > data that I have stored in an Access database. However, the only options I > > have discovered so far are to either create a mass-mailing, or to create a > > poorly formatted report in Access, then use the "publish it" function. This > > outputs again a poorly formatted document. > > > > Is it possible for me to design a form letter in Word, and have it pull the > > data for just one letter, rather than 100? > >

Re: Merging Access data into a Word document "Doug Robbins - Word MVP" <dkr[ at ]REMOVECAPSmvps.org> 7/6/2006 4:44:05 PM See response to your latter post. -- 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 "Access101" <Access101[ at ]discussions.microsoft.com> wrote in message news:6126043B-238B-4330-BAFA-55E49DF65FF8[ at ]microsoft.com... [Quoted Text] > You guys look like you know what you're talking about - slick tools. > Maybe > you know this one too: > > In my Main Merge doc I have: > <<fname>> > <<fname>> > > In the Data doc I have column heading and two names > fname > Bill > Mike > > When I merge, Bill, of course, replaces each <<fname>> with Bill and looks > like this: > Bill > Bill > What I want, is this: > Bill > Mike > > Is this possible? > > Any help is appreciated. > > "cjg.groups[ at ]gmail.com" wrote: > >> The solution depends on what level of automation that you need. >> >> In Word 2003, you can use Tools > Letters/Mailings > Mail Merge, and >> after selecting the Access table in step 3, check only the one >> recipient that you want. >> >> You may also try Albert Kallal's "MergeSingleWord". >> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html >> >> I automated the process with Visual Basic code to merge the current >> record being displayed on an Access form. The form has fields to find >> and identify the desired record, a command button to launch this code, >> and a text box bound the data table primary key called OrderID. >> >> This article was used as a foundation: >> http://support.microsoft.com/?id=209976 >> The code requires reference (Tools > References) to Word 11 Object >> Library; may need to browse for C:\PF\MS Office\Office11\msword.olb >> >> The Word template should not be bound to a data source. Bind it to a >> data source only to easily enter the merge fields, then convert it back >> to a normal Word document (Word 2000: Mail Merge Helper > 1 > Restore >> to normal Word document. Word 2003: Mail merge toolbar > Main document >> setup > Normal Word document). >> >> You may get errors from Word such as the "Data Link" window or >> complaints that Access is locked and needs an admin password. Post >> them to this thread. >> >> Here is the code. Any MVPs and pros, feel free to correct. Sorry for >> line wrapping; not sure why it does that. >> >> >> Function OutputDoc() >> >> Dim appWord As Word.Application >> Dim objWord As Word.Document >> >> Set appWord = New Word.Application >> Set objWord = appWord.Documents.Open("C:\MergeTemplate.doc") >> >> ' Make Word visible. >> appWord.Visible = True >> ' Set the mail merge data source as the database. Note the query >> parameter. >> objWord.MailMerge.OpenDataSource Name:="Database.mdb", >> LinkToSource:=True, Connection:="TABLE tblData", SQLStatement:="SELECT >> * FROM [tblData] WHERE [Orders.OrderID] = " & Forms!OrderForm!OrderID >> ' Execute the mail merge. >> objWord.MailMerge.Destination = wdSendToNewDocument >> objWord.MailMerge.Execute >> >> ' clean up and close Word, closing windows in reverse order that >> they were opened >> Set objWord = appWord.Documents("Form Letters1") >> objWord.Close wdDoNotSaveChanges >> Set objWord = appWord.Documents("Sampling Request Form - merge.doc") >> objWord.SaveAs ("C:\Output.doc") >> appWord.Quit >> Set appWord = Nothing >> End Function >> >> >> Good luck. >> >> >> Danny wrote: >> > Is it possible to take the data from Access, and merge it into just one >> > Word >> > document? I wish to create a form letter addressed to one person, using >> > the >> > data that I have stored in an Access database. However, the only >> > options I >> > have discovered so far are to either create a mass-mailing, or to >> > create a >> > poorly formatted report in Access, then use the "publish it" function. >> > This >> > outputs again a poorly formatted document. >> > >> > Is it possible for me to design a form letter in Word, and have it pull >> > the >> > data for just one letter, rather than 100? >> >>

Re: Merging Access data into a Word document Danny 7/10/2006 9:05:01 AM Very complete suggestions. You've got me past my roadblock, and I'm much appreciative. Thank you so much! "cjg.groups[ at ]gmail.com" wrote: [Quoted Text] > The solution depends on what level of automation that you need. > > In Word 2003, you can use Tools > Letters/Mailings > Mail Merge, and > after selecting the Access table in step 3, check only the one > recipient that you want. > > You may also try Albert Kallal's "MergeSingleWord". > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html > > I automated the process with Visual Basic code to merge the current > record being displayed on an Access form. The form has fields to find > and identify the desired record, a command button to launch this code, > and a text box bound the data table primary key called OrderID. > > This article was used as a foundation: > http://support.microsoft.com/?id=209976 > The code requires reference (Tools > References) to Word 11 Object > Library; may need to browse for C:\PF\MS Office\Office11\msword.olb > > The Word template should not be bound to a data source. Bind it to a > data source only to easily enter the merge fields, then convert it back > to a normal Word document (Word 2000: Mail Merge Helper > 1 > Restore > to normal Word document. Word 2003: Mail merge toolbar > Main document > setup > Normal Word document). > > You may get errors from Word such as the "Data Link" window or > complaints that Access is locked and needs an admin password. Post > them to this thread. > > Here is the code. Any MVPs and pros, feel free to correct. Sorry for > line wrapping; not sure why it does that. > > > Function OutputDoc() > > Dim appWord As Word.Application > Dim objWord As Word.Document > > Set appWord = New Word.Application > Set objWord = appWord.Documents.Open("C:\MergeTemplate.doc") > > ' Make Word visible. > appWord.Visible = True > ' Set the mail merge data source as the database. Note the query > parameter. > objWord.MailMerge.OpenDataSource Name:="Database.mdb", > LinkToSource:=True, Connection:="TABLE tblData", SQLStatement:="SELECT > * FROM [tblData] WHERE [Orders.OrderID] = " & Forms!OrderForm!OrderID > ' Execute the mail merge. > objWord.MailMerge.Destination = wdSendToNewDocument > objWord.MailMerge.Execute > > ' clean up and close Word, closing windows in reverse order that > they were opened > Set objWord = appWord.Documents("Form Letters1") > objWord.Close wdDoNotSaveChanges > Set objWord = appWord.Documents("Sampling Request Form - merge.doc") > objWord.SaveAs ("C:\Output.doc") > appWord.Quit > Set appWord = Nothing > End Function > > > Good luck. > > > Danny wrote: > > Is it possible to take the data from Access, and merge it into just one Word > > document? I wish to create a form letter addressed to one person, using the > > data that I have stored in an Access database. However, the only options I > > have discovered so far are to either create a mass-mailing, or to create a > > poorly formatted report in Access, then use the "publish it" function. This > > outputs again a poorly formatted document. > > > > Is it possible for me to design a form letter in Word, and have it pull the > > data for just one letter, rather than 100? > >

Re: Merging Access data into a Word document Michael 6/19/2007 3:03:27 PM Dear all, I have a similar problem with mail merge. I have a .doc document and a data source from an oracle database which stores HTML data as text. The front end application has no problem recognizing the HTML tags and would display the HTML texts correctly. But once I tried to mail merge this HTML text to the Word document, Word would show the HTML tags as is. i.e. <BR>test</BR> instead of a line break before 'test', a & instead of &, and a <B>bold</B> instead of a bolded 'bold' text. I wonder if there's a way to make Word interpret the HTML tags automatically during the mail merge? Thanks in advance, Michael "cjg.groups[ at ]gmail.com" wrote: [Quoted Text] > The solution depends on what level of automation that you need. > > In Word 2003, you can use Tools > Letters/Mailings > Mail Merge, and > after selecting the Access table in step 3, check only the one > recipient that you want. > > You may also try Albert Kallal's "MergeSingleWord". > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html > > I automated the process with Visual Basic code to merge the current > record being displayed on an Access form. The form has fields to find > and identify the desired record, a command button to launch this code, > and a text box bound the data table primary key called OrderID. > > This article was used as a foundation: > http://support.microsoft.com/?id=209976 > The code requires reference (Tools > References) to Word 11 Object > Library; may need to browse for C:\PF\MS Office\Office11\msword.olb > > The Word template should not be bound to a data source. Bind it to a > data source only to easily enter the merge fields, then convert it back > to a normal Word document (Word 2000: Mail Merge Helper > 1 > Restore > to normal Word document. Word 2003: Mail merge toolbar > Main document > setup > Normal Word document). > > You may get errors from Word such as the "Data Link" window or > complaints that Access is locked and needs an admin password. Post > them to this thread. > > Here is the code. Any MVPs and pros, feel free to correct. Sorry for > line wrapping; not sure why it does that. > > > Function OutputDoc() > > Dim appWord As Word.Application > Dim objWord As Word.Document > > Set appWord = New Word.Application > Set objWord = appWord.Documents.Open("C:\MergeTemplate.doc") > > ' Make Word visible. > appWord.Visible = True > ' Set the mail merge data source as the database. Note the query > parameter. > objWord.MailMerge.OpenDataSource Name:="Database.mdb", > LinkToSource:=True, Connection:="TABLE tblData", SQLStatement:="SELECT > * FROM [tblData] WHERE [Orders.OrderID] = " & Forms!OrderForm!OrderID > ' Execute the mail merge. > objWord.MailMerge.Destination = wdSendToNewDocument > objWord.MailMerge.Execute > > ' clean up and close Word, closing windows in reverse order that > they were opened > Set objWord = appWord.Documents("Form Letters1") > objWord.Close wdDoNotSaveChanges > Set objWord = appWord.Documents("Sampling Request Form - merge.doc") > objWord.SaveAs ("C:\Output.doc") > appWord.Quit > Set appWord = Nothing > End Function > > > Good luck. > > > Danny wrote: > > Is it possible to take the data from Access, and merge it into just one Word > > document? I wish to create a form letter addressed to one person, using the > > data that I have stored in an Access database. However, the only options I > > have discovered so far are to either create a mass-mailing, or to create a > > poorly formatted report in Access, then use the "publish it" function. This > > outputs again a poorly formatted document. > > > > Is it possible for me to design a form letter in Word, and have it pull the > > data for just one letter, rather than 100? > >

Re: Merging Access data into a Word document "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 6/20/2007 7:19:06 PM Michael - I have responsed (but not with the answer you seek) to your later similar question. Peter Jamieson "Michael" <Michael[ at ]discussions.microsoft.com> wrote in message news:62607DA9-81CB-413B-9740-3F64FC1657DF[ at ]microsoft.com... [Quoted Text] > Dear all, I have a similar problem with mail merge. I have a .doc document > and a data source from an oracle database which stores HTML data as text. > The > front end application has no problem recognizing the HTML tags and would > display the HTML texts correctly. But once I tried to mail merge this HTML > text to the Word document, Word would show the HTML tags as is. i.e. > <BR>test</BR> instead of a line break before 'test', a & instead of &, > and > a <B>bold</B> instead of a bolded 'bold' text. > > I wonder if there's a way to make Word interpret the HTML tags > automatically > during the mail merge? > > Thanks in advance, > Michael > > > "cjg.groups[ at ]gmail.com" wrote: > >> The solution depends on what level of automation that you need. >> >> In Word 2003, you can use Tools > Letters/Mailings > Mail Merge, and >> after selecting the Access table in step 3, check only the one >> recipient that you want. >> >> You may also try Albert Kallal's "MergeSingleWord". >> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html >> >> I automated the process with Visual Basic code to merge the current >> record being displayed on an Access form. The form has fields to find >> and identify the desired record, a command button to launch this code, >> and a text box bound the data table primary key called OrderID. >> >> This article was used as a foundation: >> http://support.microsoft.com/?id=209976 >> The code requires reference (Tools > References) to Word 11 Object >> Library; may need to browse for C:\PF\MS Office\Office11\msword.olb >> >> The Word template should not be bound to a data source. Bind it to a >> data source only to easily enter the merge fields, then convert it back >> to a normal Word document (Word 2000: Mail Merge Helper > 1 > Restore >> to normal Word document. Word 2003: Mail merge toolbar > Main document >> setup > Normal Word document). >> >> You may get errors from Word such as the "Data Link" window or >> complaints that Access is locked and needs an admin password. Post >> them to this thread. >> >> Here is the code. Any MVPs and pros, feel free to correct. Sorry for >> line wrapping; not sure why it does that. >> >> >> Function OutputDoc() >> >> Dim appWord As Word.Application >> Dim objWord As Word.Document >> >> Set appWord = New Word.Application >> Set objWord = appWord.Documents.Open("C:\MergeTemplate.doc") >> >> ' Make Word visible. >> appWord.Visible = True >> ' Set the mail merge data source as the database. Note the query >> parameter. >> objWord.MailMerge.OpenDataSource Name:="Database.mdb", >> LinkToSource:=True, Connection:="TABLE tblData", SQLStatement:="SELECT >> * FROM [tblData] WHERE [Orders.OrderID] = " & Forms!OrderForm!OrderID >> ' Execute the mail merge. >> objWord.MailMerge.Destination = wdSendToNewDocument >> objWord.MailMerge.Execute >> >> ' clean up and close Word, closing windows in reverse order that >> they were opened >> Set objWord = appWord.Documents("Form Letters1") >> objWord.Close wdDoNotSaveChanges >> Set objWord = appWord.Documents("Sampling Request Form - merge.doc") >> objWord.SaveAs ("C:\Output.doc") >> appWord.Quit >> Set appWord = Nothing >> End Function >> >> >> Good luck. >> >> >> Danny wrote: >> > Is it possible to take the data from Access, and merge it into just one >> > Word >> > document? I wish to create a form letter addressed to one person, using >> > the >> > data that I have stored in an Access database. However, the only >> > options I >> > have discovered so far are to either create a mass-mailing, or to >> > create a >> > poorly formatted report in Access, then use the "publish it" function. >> > This >> > outputs again a poorly formatted document. >> > >> > Is it possible for me to design a form letter in Word, and have it pull >> > the >> > data for just one letter, rather than 100? >> >>

Re: Merging Access data into a Word document GaryNSHC 6/21/2007 5:09:00 AM I am using this piece of code and am having a little problem with my mergefields not picking up the correct value for a YES/NO field. If I use {mergefield name ="false" ...} it doesn't work with single records but does when I use all records. BUT .. When I use {mergefield name = "0" ..} it works for single records but not for all records. Whichever way I go, I can't seem to get the word template to pay ball correctly whether I use a single record or merge all records. Has anyone else come across this, and if so, how was it resolved? Regards Gary "Doug Robbins - Word MVP" wrote: [Quoted Text] > See the "Super Easy Word Merge" item at > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html > > -- > 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

Re: Merging Access data into a Word document "Graham Mayor" <gmayor[ at ]REMOVETHISmvps.org> 6/21/2007 5:52:12 AM What exactly does {Mergefield name} produce when inserted without a condition? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> GaryNSHC wrote: [Quoted Text] > I am using this piece of code and am having a little problem with my > mergefields not picking up the correct value for a YES/NO field. > > If I use {mergefield name ="false" ...} it doesn't work with single > records but does when I use all records. > > BUT .. > > When I use {mergefield name = "0" ..} it works for single records but > not for all records. > > Whichever way I go, I can't seem to get the word template to pay ball > correctly whether I use a single record or merge all records. > > Has anyone else come across this, and if so, how was it resolved? > > Regards > > Gary > > "Doug Robbins - Word MVP" wrote: > >> See the "Super Easy Word Merge" item at >> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html >> >> -- >> 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

Re: Merging Access data into a Word document GaryNSHC 6/21/2007 6:18:00 AM My apologies, I am using an IF condition as well as a MERGEFIELD. The one which works in a single record is: {IF {mergefield MAACTeam} = "0", "No", "Yes"} and I get "NO" if I change = "0" to either = "TRUE" or = "FALSE" I get "YES" which is incorrect. The one which works for all records is: {IF {mergefield MAACTeam} = "FALSE", "No", "Yes"} I get either "YES" or "NO" if I change = "FALSE" to either = "0" or = "-1" I get "YES" for all records which is incorrect. If I have just: {mergefield MAACTeam} I get either "0" or "-1" depending on whether it should be "YES" or "NO". Hope this clarifies the problem being encountered. Regards Gary "Graham Mayor" wrote: [Quoted Text] > > What exactly does {Mergefield name} produce when inserted without a > condition? > > -- > <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Graham Mayor - Word MVP > > My web site www.gmayor.com > Word MVP web site http://word.mvps.org > <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > > GaryNSHC wrote: > > I am using this piece of code and am having a little problem with my > > mergefields not picking up the correct value for a YES/NO field. > > > > If I use {mergefield name ="false" ...} it doesn't work with single > > records but does when I use all records. > > > > BUT .. > > > > When I use {mergefield name = "0" ..} it works for single records but > > not for all records. > > > > Whichever way I go, I can't seem to get the word template to pay ball > > correctly whether I use a single record or merge all records. > > > > Has anyone else come across this, and if so, how was it resolved? > > > > Regards > > > > Gary > > > > "Doug Robbins - Word MVP" wrote: > > > >> See the "Super Easy Word Merge" item at > >> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html > >> > >> -- > >> 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 > > >

Re: Merging Access data into a Word document "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 6/21/2007 8:56:25 AM [Quoted Text] > If I have just: > {mergefield MAACTeam} I get either "0" or "-1" depending on whether it > should be "YES" or "NO". Is this actually -1 for Yes and 0 for No (in which case are you using Word 2000 or earlier and/or connecting using DDE? AFAIK the values Word receives are as follows: Connection method "Yes" "No" DDE -1 0 ODBC 0 1 OLE DB True False When you say "works for a single record" what are you doing differently? Do you mean you are connecting to a different data source that only has one record, are you using the same table/query as the data source in both merges and changing the query criteria for one of them? Or what? Also, are the Yes/No results for just { mergefield MAACTeam } always -1 and 0, or do they actually differ depending on whehter you are doing 1 record or many records? Also, are you really using "FALSE" rather than "False"? Can we assume that you have not got commas in your IF field? Do you have a bookmark called False in your Mail Merge Main Document or any included file? Does it make any difference if you quote the mergefield, e.g. { IF "{ MERGEFIELD MAACTeam }" = "0" "No" "Yes" } Peter Jamieson "GaryNSHC" <GaryNSHC[ at ]discussions.microsoft.com> wrote in message news:7EFCAD47-2B96-4BB1-B113-DCB8E39E5E7B[ at ]microsoft.com... > My apologies, I am using an IF condition as well as a MERGEFIELD. > > The one which works in a single record is: > {IF {mergefield MAACTeam} = "0", "No", "Yes"} and I get "NO" > if I change = "0" to either = "TRUE" or = "FALSE" I get "YES" which is > incorrect. > > The one which works for all records is: > {IF {mergefield MAACTeam} = "FALSE", "No", "Yes"} I get either "YES" or > "NO" > if I change = "FALSE" to either = "0" or = "-1" I get "YES" for all > records > which is incorrect. > > If I have just: > {mergefield MAACTeam} I get either "0" or "-1" depending on whether it > should be "YES" or "NO". > > Hope this clarifies the problem being encountered. > > Regards > > Gary > > "Graham Mayor" wrote: > >> >> What exactly does {Mergefield name} produce when inserted without a >> condition? >> >> -- >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> Graham Mayor - Word MVP >> >> My web site www.gmayor.com >> Word MVP web site http://word.mvps.org >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> >> GaryNSHC wrote: >> > I am using this piece of code and am having a little problem with my >> > mergefields not picking up the correct value for a YES/NO field. >> > >> > If I use {mergefield name ="false" ...} it doesn't work with single >> > records but does when I use all records. >> > >> > BUT .. >> > >> > When I use {mergefield name = "0" ..} it works for single records but >> > not for all records. >> > >> > Whichever way I go, I can't seem to get the word template to pay ball >> > correctly whether I use a single record or merge all records. >> > >> > Has anyone else come across this, and if so, how was it resolved? >> > >> > Regards >> > >> > Gary >> > >> > "Doug Robbins - Word MVP" wrote: >> > >> >> See the "Super Easy Word Merge" item at >> >> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html >> >> >> >> -- >> >> 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 >> >> >>

Re: Merging Access data into a Word document GaryNSHC 6/21/2007 10:45:00 AM Peter, As far as I know: I believe it is Word 2000, and again my apologies, there is no comma between the values (hangover from coding excel IF statement). I think the data source is exported as a file (merge.888) which is then used to create the merge document. I don't think it is directly linked with Access. The values are always -1 or 0 depending on what the source data in access is, depending on whether I am trying to merge one record or all records. I have about 15 access fields with "Yes" or "No" answers which are being merged. The fields indicate what activities the person participates in within our organisation. I'll try quoting the meregfield and see if it makes a difference. Thanks for trying to sort this out for me. Regards Gary "Peter Jamieson" wrote: [Quoted Text] > > If I have just: > > {mergefield MAACTeam} I get either "0" or "-1" depending on whether it > > should be "YES" or "NO". > > Is this actually -1 for Yes and 0 for No (in which case are you using Word > 2000 or earlier and/or connecting using DDE? AFAIK the values Word receives > are as follows: > > Connection method "Yes" "No" > DDE -1 0 > ODBC 0 1 > OLE DB True False > > When you say "works for a single record" what are you doing differently? Do > you mean you are connecting to a different data source that only has one > record, are you using the same table/query as the data source in both merges > and changing the query criteria for one of them? Or what? Also, are the > Yes/No results for just > > { mergefield MAACTeam } > > always -1 and 0, or do they actually differ depending on whehter you are > doing 1 record or many records? > > Also, are you really using "FALSE" rather than "False"? Can we assume that > you have not got commas in your IF field? Do you have a bookmark called > False in your Mail Merge Main Document or any included file? Does it make > any difference if you quote the mergefield, e.g. > > { IF "{ MERGEFIELD MAACTeam }" = "0" "No" "Yes" } > > Peter Jamieson > > > "GaryNSHC" <GaryNSHC[ at ]discussions.microsoft.com> wrote in message > news:7EFCAD47-2B96-4BB1-B113-DCB8E39E5E7B[ at ]microsoft.com... > > My apologies, I am using an IF condition as well as a MERGEFIELD. > > > > The one which works in a single record is: > > {IF {mergefield MAACTeam} = "0", "No", "Yes"} and I get "NO" > > if I change = "0" to either = "TRUE" or = "FALSE" I get "YES" which is > > incorrect. > > > > The one which works for all records is: > > {IF {mergefield MAACTeam} = "FALSE", "No", "Yes"} I get either "YES" or > > "NO" > > if I change = "FALSE" to either = "0" or = "-1" I get "YES" for all > > records > > which is incorrect. > > > > If I have just: > > {mergefield MAACTeam} I get either "0" or "-1" depending on whether it > > should be "YES" or "NO". > > > > Hope this clarifies the problem being encountered. > > > > Regards > > > > Gary > > > > "Graham Mayor" wrote: > > > >> > >> What exactly does {Mergefield name} produce when inserted without a > >> condition? > >> > >> -- > >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > >> Graham Mayor - Word MVP > >> > >> My web site www.gmayor.com > >> Word MVP web site http://word.mvps.org > >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > >> > >> GaryNSHC wrote: > >> > I am using this piece of code and am having a little problem with my > >> > mergefields not picking up the correct value for a YES/NO field. > >> > > >> > If I use {mergefield name ="false" ...} it doesn't work with single > >> > records but does when I use all records. > >> > > >> > BUT .. > >> > > >> > When I use {mergefield name = "0" ..} it works for single records but > >> > not for all records. > >> > > >> > Whichever way I go, I can't seem to get the word template to pay ball > >> > correctly whether I use a single record or merge all records. > >> > > >> > Has anyone else come across this, and if so, how was it resolved? > >> > > >> > Regards > >> > > >> > Gary > >> > > >> > "Doug Robbins - Word MVP" wrote: > >> > > >> >> See the "Super Easy Word Merge" item at > >> >> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html > >> >> > >> >> -- > >> >> 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 > >> > >> > >> > >

Re: Merging Access data into a Word document "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 6/21/2007 1:41:59 PM I can just about see circumstances in which this might occur, but the case isn't particularly convincing, which suggests that the explanation is likely to be staring us in the face :-) I gather you're working at a distance from the problem so it may be difficult to check stuff and experiment. It seems to me that whatever may be in the Access file or intermediate file, Word will only compare correctly with "0" if the YesNo value /it reads/ is "0", and will only compare correctly with "False" if the values it actually sees are "False" and "True". Which isn't what you actually say, but I wonder whether that is actually the case. As far as I know, if Word is getting its data from a text file, the only way it would see -1 as "True" is if a. it's opening the file using OLE DB (i.e. it's Word 2002 or later) b. there's a SCHEMA.INI file in the same folder as the text file that specifies that the column has type "Bit". This could well be the case because when you export from Access, Access does create such entries in a SCHEMA.INI - at least sometimes. If someone is using an Access module or macro to export, then it may also specify a particular export layout. However, the hypothesis also has to explain why you see -1/0 when you have a data source with 1 record, but True/False when it has many records. Possibilities include a. Word is opening the file using OLE DB but only recognises that the column is "Yes/No" type (and provides True/False instead of -1/0 when there are more than so many records in the file - perhaps 8 or 25. I don't see this here. b. Access sets up a SCHEMA.INI entry when it exports multiple records, but not a single record. (In fact, Access itself does not appear to make this distinction, although if the export is being done through code, perhaps something else is doing something differently. What I see is bizarre: when you export manually, if you specify a file extension (e.g. .txt) Access just exports the data and modifes SCHEMA.INI. If you do not, Access takes you through the export layout dialog and /does not/ modify SCHEMA.INI) c. Word tries to connect to the one-row data files using OLE DB (the defaul) but fails and falls back to using its Text converter. With the multi-row files, it succeeds. This is an unlikely way around. Even if the right explanation is in there somewhere, there's the question of what you do about it and how to find out exactly what's going. What I would probably prefer to do is avoid the problem altoggether by trying to export the data using the string values "Y" and "N" instead of numeric/boolean values. If I had to determine connection method etc., I'd probably use Word VBA to find the values of ActiveDocument.MailMerge.DataSource.COnnectString, ..QueryString and .Type /after/ the connection had been made. Best I can right now... Peter Jamieson "GaryNSHC" <GaryNSHC[ at ]discussions.microsoft.com> wrote in message news:3F84F841-D4E1-4928-80D3-97F7852AA1DA[ at ]microsoft.com... [Quoted Text] > Peter, > > As far as I know: > > I believe it is Word 2000, and again my apologies, there is no comma > between > the values (hangover from coding excel IF statement). > > I think the data source is exported as a file (merge.888) which is then > used > to create the merge document. I don't think it is directly linked with > Access. > > The values are always -1 or 0 depending on what the source data in access > is, depending on whether I am trying to merge one record or all records. I > have about 15 access fields with "Yes" or "No" answers which are being > merged. The fields indicate what activities the person participates in > within > our organisation. > > I'll try quoting the meregfield and see if it makes a difference. > > Thanks for trying to sort this out for me. > > Regards > > Gary > > "Peter Jamieson" wrote: > >> > If I have just: >> > {mergefield MAACTeam} I get either "0" or "-1" depending on whether it >> > should be "YES" or "NO". >> >> Is this actually -1 for Yes and 0 for No (in which case are you using >> Word >> 2000 or earlier and/or connecting using DDE? AFAIK the values Word >> receives >> are as follows: >> >> Connection method "Yes" "No" >> DDE -1 0 >> ODBC 0 1 >> OLE DB True False >> >> When you say "works for a single record" what are you doing differently? >> Do >> you mean you are connecting to a different data source that only has one >> record, are you using the same table/query as the data source in both >> merges >> and changing the query criteria for one of them? Or what? Also, are the >> Yes/No results for just >> >> { mergefield MAACTeam } >> >> always -1 and 0, or do they actually differ depending on whehter you are >> doing 1 record or many records? >> >> Also, are you really using "FALSE" rather than "False"? Can we assume >> that >> you have not got commas in your IF field? Do you have a bookmark called >> False in your Mail Merge Main Document or any included file? Does it make >> any difference if you quote the mergefield, e.g. >> >> { IF "{ MERGEFIELD MAACTeam }" = "0" "No" "Yes" } >> >> Peter Jamieson >> >> >> "GaryNSHC" <GaryNSHC[ at ]discussions.microsoft.com> wrote in message >> news:7EFCAD47-2B96-4BB1-B113-DCB8E39E5E7B[ at ]microsoft.com... >> > My apologies, I am using an IF condition as well as a MERGEFIELD. >> > >> > The one which works in a single record is: >> > {IF {mergefield MAACTeam} = "0", "No", "Yes"} and I get "NO" >> > if I change = "0" to either = "TRUE" or = "FALSE" I get "YES" which is >> > incorrect. >> > >> > The one which works for all records is: >> > {IF {mergefield MAACTeam} = "FALSE", "No", "Yes"} I get either "YES" >> > or >> > "NO" >> > if I change = "FALSE" to either = "0" or = "-1" I get "YES" for all >> > records >> > which is incorrect. >> > >> > If I have just: >> > {mergefield MAACTeam} I get either "0" or "-1" depending on whether it >> > should be "YES" or "NO". >> > >> > Hope this clarifies the problem being encountered. >> > >> > Regards >> > >> > Gary >> > >> > "Graham Mayor" wrote: >> > >> >> >> >> What exactly does {Mergefield name} produce when inserted without a >> >> condition? >> >> >> >> -- >> >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> >> Graham Mayor - Word MVP >> >> >> >> My web site www.gmayor.com >> >> Word MVP web site http://word.mvps.org >> >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> >> >> >> GaryNSHC wrote: >> >> > I am using this piece of code and am having a little problem with my >> >> > mergefields not picking up the correct value for a YES/NO field. >> >> > >> >> > If I use {mergefield name ="false" ...} it doesn't work with single >> >> > records but does when I use all records. >> >> > >> >> > BUT .. >> >> > >> >> > When I use {mergefield name = "0" ..} it works for single records >> >> > but >> >> > not for all records. >> >> > >> >> > Whichever way I go, I can't seem to get the word template to pay >> >> > ball >> >> > correctly whether I use a single record or merge all records. >> >> > >> >> > Has anyone else come across this, and if so, how was it resolved? >> >> > >> >> > Regards >> >> > >> >> > Gary >> >> > >> >> > "Doug Robbins - Word MVP" wrote: >> >> > >> >> >> See the "Super Easy Word Merge" item at >> >> >> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html >> >> >> >> >> >> -- >> >> >> 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 >> >> >> >> >> >> >> >>

Re: Merging Access data into a Word document GaryNSHC 6/22/2007 12:14:00 AM Peter, [Quoted Text] > However, the hypothesis also has to explain why you see -1/0 when you have a > data source with 1 record, but True/False when it has many records. Just to clarify, depending on whether the field condition is "0" or "false", and depending on whether 1 record or all records are merged, one merge will merge fields correctly, whilst the other merge will merge all fields with one value - whether it is the right or wrong value. Your technical knowledge is way beyond my comprehension unfortunately. I will try some of your suggestions from your first resposne to see if that makes a difference. Regards Gary "Peter Jamieson" wrote: > I can just about see circumstances in which this might occur, but the case > isn't particularly convincing, which suggests that the explanation is likely > to be staring us in the face :-) I gather you're working at a distance from > the problem so it may be difficult to check stuff and experiment. > > It seems to me that whatever may be in the Access file or intermediate file, > Word will only compare correctly with "0" if the YesNo value /it reads/ is > "0", and will only compare correctly with "False" if the values it actually > sees are "False" and "True". Which isn't what you actually say, but I wonder > whether that is actually the case. > > As far as I know, if Word is getting its data from a text file, the only way > it would see -1 as "True" is if > a. it's opening the file using OLE DB (i.e. it's Word 2002 or later) > b. there's a SCHEMA.INI file in the same folder as the text file that > specifies that the column has type "Bit". This could well be the case > because when you export from Access, Access does create such entries in a > SCHEMA.INI - at least sometimes. If someone is using an Access module or > macro to export, then it may also specify a particular export layout. > > However, the hypothesis also has to explain why you see -1/0 when you have a > data source with 1 record, but True/False when it has many records. > Possibilities include > a. Word is opening the file using OLE DB but only recognises that the > column is "Yes/No" type (and provides True/False instead of -1/0 when there > are more than so many records in the file - perhaps 8 or 25. I don't see > this here. > b. Access sets up a SCHEMA.INI entry when it exports multiple records, but > not a single record. (In fact, Access itself does not appear to make this > distinction, although if the export is being done through code, perhaps > something else is doing something differently. What I see is bizarre: when > you export manually, if you specify a file extension (e.g. .txt) Access just > exports the data and modifes SCHEMA.INI. If you do not, Access takes you > through the export layout dialog and /does not/ modify SCHEMA.INI) > c. Word tries to connect to the one-row data files using OLE DB (the > defaul) but fails and falls back to using its Text converter. With the > multi-row files, it succeeds. This is an unlikely way around. > > Even if the right explanation is in there somewhere, there's the question of > what you do about it and how to find out exactly what's going. What I would > probably prefer to do is avoid the problem altoggether by trying to export > the data using the string values "Y" and "N" instead of numeric/boolean > values. If I had to determine connection method etc., I'd probably use Word > VBA to find the values of ActiveDocument.MailMerge.DataSource.COnnectString, > ..QueryString and .Type /after/ the connection had been made. > > Best I can right now... > > Peter Jamieson > "GaryNSHC" <GaryNSHC[ at ]discussions.microsoft.com> wrote in message > news:3F84F841-D4E1-4928-80D3-97F7852AA1DA[ at ]microsoft.com... > > Peter, > > > > As far as I know: > > > > I believe it is Word 2000, and again my apologies, there is no comma > > between > > the values (hangover from coding excel IF statement). > > > > I think the data source is exported as a file (merge.888) which is then > > used > > to create the merge document. I don't think it is directly linked with > > Access. > > > > The values are always -1 or 0 depending on what the source data in access > > is, depending on whether I am trying to merge one record or all records. I > > have about 15 access fields with "Yes" or "No" answers which are being > > merged. The fields indicate what activities the person participates in > > within > > our organisation. > > > > I'll try quoting the meregfield and see if it makes a difference. > > > > Thanks for trying to sort this out for me. > > > > Regards > > > > Gary > > > > "Peter Jamieson" wrote: > > > >> > If I have just: > >> > {mergefield MAACTeam} I get either "0" or "-1" depending on whether it > >> > should be "YES" or "NO". > >> > >> Is this actually -1 for Yes and 0 for No (in which case are you using > >> Word > >> 2000 or earlier and/or connecting using DDE? AFAIK the values Word > >> receives > >> are as follows: > >> > >> Connection method "Yes" "No" > >> DDE -1 0 > >> ODBC 0 1 > >> OLE DB True False > >> > >> When you say "works for a single record" what are you doing differently? > >> Do > >> you mean you are connecting to a different data source that only has one > >> record, are you using the same table/query as the data source in both > >> merges > >> and changing the query criteria for one of them? Or what? Also, are the > >> Yes/No results for just > >> > >> { mergefield MAACTeam } > >> > >> always -1 and 0, or do they actually differ depending on whehter you are > >> doing 1 record or many records? > >> > >> Also, are you really using "FALSE" rather than "False"? Can we assume > >> that > >> you have not got commas in your IF field? Do you have a bookmark called > >> False in your Mail Merge Main Document or any included file? Does it make > >> any difference if you quote the mergefield, e.g. > >> > >> { IF "{ MERGEFIELD MAACTeam }" = "0" "No" "Yes" } > >> > >> Peter Jamieson > >> > >> > >> "GaryNSHC" <GaryNSHC[ at ]discussions.microsoft.com> wrote in message > >> news:7EFCAD47-2B96-4BB1-B113-DCB8E39E5E7B[ at ]microsoft.com... > >> > My apologies, I am using an IF condition as well as a MERGEFIELD. > >> > > >> > The one which works in a single record is: > >> > {IF {mergefield MAACTeam} = "0", "No", "Yes"} and I get "NO" > >> > if I change = "0" to either = "TRUE" or = "FALSE" I get "YES" which is > >> > incorrect. > >> > > >> > The one which works for all records is: > >> > {IF {mergefield MAACTeam} = "FALSE", "No", "Yes"} I get either "YES" > >> > or > >> > "NO" > >> > if I change = "FALSE" to either = "0" or = "-1" I get "YES" for all > >> > records > >> > which is incorrect. > >> > > >> > If I have just: > >> > {mergefield MAACTeam} I get either "0" or "-1" depending on whether it > >> > should be "YES" or "NO". > >> > > >> > Hope this clarifies the problem being encountered. > >> > > >> > Regards > >> > > >> > Gary > >> > > >> > "Graham Mayor" wrote: > >> > > >> >> > >> >> What exactly does {Mergefield name} produce when inserted without a > >> >> condition? > >> >> > >> >> -- > >> >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > >> >> Graham Mayor - Word MVP > >> >> > >> >> My web site www.gmayor.com > >> >> Word MVP web site http://word.mvps.org > >> >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > >> >> > >> >> GaryNSHC wrote: > >> >> > I am using this piece of code and am having a little problem with my > >> >> > mergefields not picking up the correct value for a YES/NO field. > >> >> > > >> >> > If I use {mergefield name ="false" ...} it doesn't work with single > >> >> > records but does when I use all records. > >> >> > > >> >> > BUT .. > >> >> > > >> >> > When I use {mergefield name = "0" ..} it works for single records > >> >> > but > >> >> > not for all records. > >> >> > > >> >> > Whichever way I go, I can't seem to get the word template to pay > >> >> > ball > >> >> > correctly whether I use a single record or merge all records. > >> >> > > >> >> > Has anyone else come across this, and if so, how was it resolved? > >> >> > > >> >> > Regards > >> >> > > >> >> > Gary > >> >> > > >> >> > "Doug Robbins - Word MVP" wrote: > >> >> > > >> >> >> See the "Super Easy Word Merge" item at > >> >> >> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html > >> >> >> > >> >> >> -- > >> >> >> 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 > >> >> > >> >> > >> >> > >> > >> > >

Re: Merging Access data into a Word document GaryNSHC 6/22/2007 1:25:00 AM Hi all, Whilst probably not the most elegant solution, what I have found that works is a "double if" function which checks whether the value is "0" first, if so then it will display "No", if the value is not "0", a second if function will determine if the value is "false" and display "No", otherwise it will display "Yes". Regards Gary "GaryNSHC" wrote: [Quoted Text] > I am using this piece of code and am having a little problem with my > mergefields not picking up the correct value for a YES/NO field. > > If I use {mergefield name ="false" ...} it doesn't work with single records > but does when I use all records. > > BUT .. > > When I use {mergefield name = "0" ..} it works for single records but not > for all records. > > Whichever way I go, I can't seem to get the word template to pay ball > correctly whether I use a single record or merge all records. > > Has anyone else come across this, and if so, how was it resolved? > > Regards > > Gary > > "Doug Robbins - Word MVP" wrote: > > > See the "Super Easy Word Merge" item at > > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html > > > > -- > > 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

Re: Merging Access data into a Word document "Peter Jamieson" <pjj[ at ]KillmapSpjjnet.demon.co.uk> 6/22/2007 5:35:36 AM Looks like you have found a solution but if you can send samples I would be happy to have a look at them - you would need to remove "KillmapS" from my email address. Peter Jamieson "GaryNSHC" <GaryNSHC[ at ]discussions.microsoft.com> wrote in message news:A5CF8ED9-2CF2-43C9-A505-DE048B6B413E[ at ]microsoft.com... [Quoted Text] > Peter, > >> However, the hypothesis also has to explain why you see -1/0 when you >> have a >> data source with 1 record, but True/False when it has many records. > > Just to clarify, depending on whether the field condition is "0" or > "false", > and depending on whether 1 record or all records are merged, one merge > will > merge fields correctly, whilst the other merge will merge all fields with > one > value - whether it is the right or wrong value. > > Your technical knowledge is way beyond my comprehension unfortunately. I > will try some of your suggestions from your first resposne to see if that > makes a difference. > > Regards > > Gary > > "Peter Jamieson" wrote: > >> I can just about see circumstances in which this might occur, but the >> case >> isn't particularly convincing, which suggests that the explanation is >> likely >> to be staring us in the face :-) I gather you're working at a distance >> from >> the problem so it may be difficult to check stuff and experiment. >> >> It seems to me that whatever may be in the Access file or intermediate >> file, >> Word will only compare correctly with "0" if the YesNo value /it reads/ >> is >> "0", and will only compare correctly with "False" if the values it >> actually >> sees are "False" and "True". Which isn't what you actually say, but I >> wonder >> whether that is actually the case. >> >> As far as I know, if Word is getting its data from a text file, the only >> way >> it would see -1 as "True" is if >> a. it's opening the file using OLE DB (i.e. it's Word 2002 or later) >> b. there's a SCHEMA.INI file in the same folder as the text file that >> specifies that the column has type "Bit". This could well be the case >> because when you export from Access, Access does create such entries in a >> SCHEMA.INI - at least sometimes. If someone is using an Access module or >> macro to export, then it may also specify a particular export layout. >> >> However, the hypothesis also has to explain why you see -1/0 when you >> have a >> data source with 1 record, but True/False when it has many records. >> Possibilities include >> a. Word is opening the file using OLE DB but only recognises that the >> column is "Yes/No" type (and provides True/False instead of -1/0 when >> there >> are more than so many records in the file - perhaps 8 or 25. I don't see >> this here. >> b. Access sets up a SCHEMA.INI entry when it exports multiple records, >> but >> not a single record. (In fact, Access itself does not appear to make this >> distinction, although if the export is being done through code, perhaps >> something else is doing something differently. What I see is bizarre: >> when >> you export manually, if you specify a file extension (e.g. .txt) Access >> just >> exports the data and modifes SCHEMA.INI. If you do not, Access takes you >> through the export layout dialog and /does not/ modify SCHEMA.INI) >> c. Word tries to connect to the one-row data files using OLE DB (the >> defaul) but fails and falls back to using its Text converter. With the >> multi-row files, it succeeds. This is an unlikely way around. >> >> Even if the right explanation is in there somewhere, there's the question >> of >> what you do about it and how to find out exactly what's going. What I >> would >> probably prefer to do is avoid the problem altoggether by trying to >> export >> the data using the string values "Y" and "N" instead of numeric/boolean >> values. If I had to determine connection method etc., I'd probably use >> Word >> VBA to find the values of >> ActiveDocument.MailMerge.DataSource.COnnectString, >> ..QueryString and .Type /after/ the connection had been made. >> >> Best I can right now... >> >> Peter Jamieson >> "GaryNSHC" <GaryNSHC[ at ]discussions.microsoft.com> wrote in message >> news:3F84F841-D4E1-4928-80D3-97F7852AA1DA[ at ]microsoft.com... >> > Peter, >> > >> > As far as I know: >> > >> > I believe it is Word 2000, and again my apologies, there is no comma >> > between >> > the values (hangover from coding excel IF statement). >> > >> > I think the data source is exported as a file (merge.888) which is then >> > used >> > to create the merge document. I don't think it is directly linked with >> > Access. >> > >> > The values are always -1 or 0 depending on what the source data in >> > access >> > is, depending on whether I am trying to merge one record or all >> > records. I >> > have about 15 access fields with "Yes" or "No" answers which are being >> > merged. The fields indicate what activities the person participates in >> > within >> > our organisation. >> > >> > I'll try quoting the meregfield and see if it makes a difference. >> > >> > Thanks for trying to sort this out for me. >> > >> > Regards >> > >> > Gary >> > >> > "Peter Jamieson" wrote: >> > >> >> > If I have just: >> >> > {mergefield MAACTeam} I get either "0" or "-1" depending on whether >> >> > it >> >> > should be "YES" or "NO". >> >> >> >> Is this actually -1 for Yes and 0 for No (in which case are you using >> >> Word >> >> 2000 or earlier and/or connecting using DDE? AFAIK the values Word >> >> receives >> >> are as follows: >> >> >> >> Connection method "Yes" "No" >> >> DDE -1 0 >> >> ODBC 0 1 >> >> OLE DB True False >> >> >> >> When you say "works for a single record" what are you doing >> >> differently? >> >> Do >> >> you mean you are connecting to a different data source that only has >> >> one >> >> record, are you using the same table/query as the data source in both >> >> merges >> >> and changing the query criteria for one of them? Or what? Also, are >> >> the >> >> Yes/No results for just >> >> >> >> { mergefield MAACTeam } >> >> >> >> always -1 and 0, or do they actually differ depending on whehter you >> >> are >> >> doing 1 record or many records? >> >> >> >> Also, are you really using "FALSE" rather than "False"? Can we assume >> >> that >> >> you have not got commas in your IF field? Do you have a bookmark >> >> called >> >> False in your Mail Merge Main Document or any included file? Does it >> >> make >> >> any difference if you quote the mergefield, e.g. >> >> >> >> { IF "{ MERGEFIELD MAACTeam }" = "0" "No" "Yes" } >> >> >> >> Peter Jamieson >> >> >> >> >> >> "GaryNSHC" <GaryNSHC[ at ]discussions.microsoft.com> wrote in message >> >> news:7EFCAD47-2B96-4BB1-B113-DCB8E39E5E7B[ at ]microsoft.com... >> >> > My apologies, I am using an IF condition as well as a MERGEFIELD. >> >> > >> >> > The one which works in a single record is: >> >> > {IF {mergefield MAACTeam} = "0", "No", "Yes"} and I get "NO" >> >> > if I change = "0" to either = "TRUE" or = "FALSE" I get "YES" which >> >> > is >> >> > incorrect. >> >> > >> >> > The one which works for all records is: >> >> > {IF {mergefield MAACTeam} = "FALSE", "No", "Yes"} I get either >> >> > "YES" >> >> > or >> >> > "NO" >> >> > if I change = "FALSE" to either = "0" or = "-1" I get "YES" for all >> >> > records >> >> > which is incorrect. >> >> > >> >> > If I have just: >> >> > {mergefield MAACTeam} I get either "0" or "-1" depending on whether >> >> > it >> >> > should be "YES" or "NO". >> >> > >> >> > Hope this clarifies the problem being encountered. >> >> > >> >> > Regards >> >> > >> >> > Gary >> >> > >> >> > "Graham Mayor" wrote: >> >> > >> >> >> >> >> >> What exactly does {Mergefield name} produce when inserted without a >> >> >> condition? >> >> >> >> >> >> -- >> >> >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> >> >> Graham Mayor - Word MVP >> >> >> >> >> >> My web site www.gmayor.com >> >> >> Word MVP web site http://word.mvps.org >> >> >> <>>< ><<> ><<> <>>< ><<> <>>< <>><<> >> >> >> >> >> >> GaryNSHC wrote: >> >> >> > I am using this piece of code and am having a little problem with >> >> >> > my >> >> >> > mergefields not picking up the correct value for a YES/NO field. >> >> >> > >> >> >> > If I use {mergefield name ="false" ...} it doesn't work with >> >> >> > single >> >> >> > records but does when I use all records. >> >> >> > >> >> >> > BUT .. >> >> >> > >> >> >> > When I use {mergefield name = "0" ..} it works for single records >> >> >> > but >> >> >> > not for all records. >> >> >> > >> >> >> > Whichever way I go, I can't seem to get the word template to pay >> >> >> > ball >> >> >> > correctly whether I use a single record or merge all records. >> >> >> > >> >> >> > Has anyone else come across this, and if so, how was it resolved? >> >> >> > >> >> >> > Regards >> >> >> > >> >> >> > Gary >> >> >> > >> >> >> > "Doug Robbins - Word MVP" wrote: >> >> >> > >> >> >> >> See the "Super Easy Word Merge" item at >> >> >> >> http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html >> >> >> >> >> >> >> >> -- >> >> >> >> 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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >>