 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / February 2008Using dates during mail merge
Can anybody help please? Using Word 2000 (9.0.2760) we are in the process of setting up a mail merge using a standard letter for the main document. The data source comprises in each record, name, address and other data fields. Data will accumulate slowly in each record, but we need to carry out mail merges at frequent intervals. Letters generated during each mail merge must be dated with the current date, but each date must be retained in the relevant data source records for generating ‘directories/catalogues’ at a later time (using separate mail merges) for review. It would also be beneficial for each mail merge to produce only those letters with the current date, ie. not all record are merged. How can this be done, please? Many thanks in anticipation. What are you using for the data source? Frankly, it does not sound to me like mail merge is really the best option for this. How are you proposing to retain each date in the data source? In a new record? In separate fields in each record, or multiple dates in one field? These sort of things need to be considered in the first instance.  SignatureHope 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 > Can anybody help please? > [quoted text clipped - 18 lines] > > Many thanks in anticipation. Thanks for the reply. For the data source we are using a standard word doc generated by mail merge, using standard fields for names, address, etc, and we assumed that dates would be retained in separate fields in each record. For the moment, until the 'system' is set up, we are entering the current date manually in date fields for relevant records, then using skip-if (not equal to this date) to merge only those records. This retains the date but is crude indeed, as the skip-if date criterion has to be changed for each merge. There must be a better way. Hope you can help to streamline the process. Thanks. >What are you using for the data source? Frankly, it does not sound to me >like mail merge is really the best option for this. How are you proposing [quoted text clipped - 8 lines] >> >> Many thanks in anticipation. You can compare a date in a field with the current date with your skipif field {SKIPIF {Mergefield DateField \@ "yyyyMMdd"} <> {Date \@ "yyyyMMdd"}} Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Thanks for the reply. > [quoted text clipped - 23 lines] >>> >>> Many thanks in anticipation. Many thanks indeed. This works fine. However, to go one step further: Is it possible to compare the current date with a DateField that is formatted (typed) dd+ordinals MMMMyyyyy? >You can compare a date in a field with the current date with your skipif >field [quoted text clipped - 6 lines] >>>> >>>> Many thanks in anticipation. I am pretty sure that it is not possible. Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Many thanks indeed. This works fine. However, to go one step further: > Is it possible to compare the current date with a DateField that is [quoted text clipped - 10 lines] >>>>> >>>>> Many thanks in anticipation. Yes, you are correct. Please see my reply to Peter Jamieson's suggestion. My thanks to you for the further advice. >I am pretty sure that it is not possible. > [quoted text clipped - 3 lines] >>>>>> >>>>>> Many thanks in anticipation. You mean compare with things that are in your data source like 7th February2008 1st March2008 etc? As long as you can use date and numeric field switches that generate precisely the date format you need to compare with, you should be able to use a simlar technique, e.g. { SKIPIF "{ MERGEFIELD mydatetext }" <> "{ DATE \@"D" \*Ordinal }{ DATE \@" MMMMYYYY" }" } Haven't tried it myself though, and at best you will only be able to do exact comparisons (equal or not equal), not determine whether one date is before the other. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Many thanks indeed. This works fine. However, to go one step further: Is > it [quoted text clipped - 11 lines] >>>>> >>>>> Many thanks in anticipation. Yes, this is the comparison that I wish to make, preferably with a space between month and year. And yes, we wish to make a ‘not equal’ comparison. I tried your suggestion, but the merge returned nil results. Word date format (language ‘English (UK)’) was set to ‘dd MMMM yyyy’ without ordinal. However, this may have no relevance. Unless your suggested SKIPIF syntax can be modified, I suppose it will not be possible to achieve my aim. If so, I shall continue to use SKIPIF with the comparison date manually inserted at each merge. This will be crude, but the method will work. Many thanks indeed for your advice. It is very useful. >You mean compare with things that are in your data source like > [quoted text clipped - 19 lines] >>>>>> >>>>>> Many thanks in anticipation. You can add an ask field to collect the date at the start of the merge eg { ASK MyDate "Start Date" \d { Date \@ "dd/MM/yyyy} \o }{ SKIPIF { Mergefield Date \@ "yyyyMMdd"} <> { REF MyDate \@ "yyyyMMdd "} } The default date in that field is today's date, but it can be changed at the prompt. Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Yes, this is the comparison that I wish to make, preferably with a > space between month and year. And yes, we wish to make a 'not equal' [quoted text clipped - 34 lines] >>>>>>> >>>>>>> Many thanks in anticipation. OK, it certainly works here, i.e. in principle, so maybe the detail needs checking: > preferably with a space > between month and year. I suspected as much :-) { SKIPIF "{ MERGEFIELD mydatetext }" <> "{ DATE \@"D" \*Ordinal }{ DATE \@" MMMM YYYY" }" } works OK here with that spec. All the {} have to be ctrl-F9 braces as usual, and the spacing and quoting is obviously import - either one space between the two DATE fields and \@"MMMM YYYY" or no space between the two DATE fields and \@" MMMM YYYY" SignaturePeter Jamieson http://tips.pjmsn.me.uk > Yes, this is the comparison that I wish to make, preferably with a space > between month and year. And yes, we wish to make a ‘not equal’ comparison. [quoted text clipped - 38 lines] >>>>>>> >>>>>>> Many thanks in anticipation. Hmmmm. I couldn't get it to work in Word 2003 - though I didn't have the quotes around "{ MERGEFIELD mydatetext }" I'll play again tomorrow :( Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > OK, it certainly works here, i.e. in principle, so maybe the detail > needs checking: [quoted text clipped - 63 lines] >> -- >> Message posted via http://www.officekb.com Thanks Graham - I was using 2007 but will try 2003 now. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Hmmmm. I couldn't get it to work in Word 2003 - though I didn't have the > quotes around "{ MERGEFIELD mydatetext }" [quoted text clipped - 67 lines] >>> -- >>> Message posted via http://www.officekb.com Seems OK in both 2003 and 2000, with or without the quotes, which leads me to wonder whether we are attempting the same thing and/or whether I have missed a vital piece of information. SKIPIF can certainly be touchy in some cases. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Thanks Graham - I was using 2007 but will try 2003 now. > [quoted text clipped - 69 lines] >>>> -- >>>> Message posted via http://www.officekb.com It appears there were two issues that prevented this from working here as intended 1. I was using an ASK field to collect the date to compare thus { ASK MyDate "Start Date" \d { Date \@ "dd/MM/yyyy} \o }{ SKIPIF { MERGEFIELD Date2 } <> "{ REF MyDate \@ "d " \*Ordinal}{ REF MyDate \@ " MMMM yyyy" }" } Unfortunately I was using one of my test data files which also had a field called MyDate (unused in the merge) which conflicted with the bookmark of the same name. With that fieldname changed it worked. 2. This construction will not work for a range of dates eg { ASK MyDate "Start Date" \d { Date \@ "dd/MM/yyyy} \o }{ SKIPIF { MERGEFIELD Date2 } > "{ REF MyDate \@ "d " \*Ordinal}{ REF MyDate \@ " MMMM yyyy" }" } for that type of range you cannot compare dates with ordinal fields and must revert to the switch I posted earlier. You cannot derive a date in the format \@ "yyyyMMdd" from a field containing ordinal text. Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Seems OK in both 2003 and 2000, with or without the quotes, which > leads me to wonder whether we are attempting the same thing and/or [quoted text clipped - 86 lines] >>>>> -- >>>>> Message posted via http://www.officekb.com Many thanks indded for this. I will try it out over the coming weekend and let you know the result. I am very grateful for your time >It appears there were two issues that prevented this from working here as >intended [quoted text clipped - 24 lines] >>>>>> -- >>>>>> Message posted via http://www.officekb.com Many thanks for this. I tried it out but couldn't make it work. I even tried 'tweaking' spaces and quotes (very much groping in the dark) to see it this would do the trick. You included 'MyDate' and 'Date2' in your construction but I cannot understand the function of 'Date2'. It is probably this element that I have got wrong. Peter Jamieson has suggested a solution that I have been able to make work. This is great and really deals with my oringinal request. However, it is irritating that I have not been able to make your 'ASK' routine to function properly. If you have time to clarify, I shall be grateful. Please don'y bother if you are too busy. Many thanks again. >It appears there were two issues that prevented this from working here as >intended [quoted text clipped - 24 lines] >>>>>> -- >>>>>> Message posted via http://www.officekb.com Where Graham has "date2" you need to put the name of the field in the data source that you want to compare with (i.e. that has the date in "1st February 2008" format) (i.e. Graham's example has used the name "date2" where my example has used "mydatetext", but in both cases you need to substitute the name in your data source.) Other than that, AFAICS, Graham's fields should work - if you change the two { DATE } fields in my example so that instead of looking like { DATE .... } they look like { REF MyDate .... } you should be there. In theory there's a typo (unusual for him :-) ) in Graham's code: { ASK MyDate "Start Date" \d { Date \@ "dd/MM/yyyy} \o } should have an extra quote: { ASK MyDate "Start Date" \d { Date \@ "dd/MM/yyyy" } \o } but in practice it works as it is. I'd select your field codes and press F9 to re-execute them after you change them. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Many thanks for this. I tried it out but couldn't make it work. I even > tried [quoted text clipped - 44 lines] >>>>>>> -- >>>>>>> Message posted via http://www.officekb.com Re the typo - I use the macro at http://www.gmayor.com/export_field.htm to export field contructions to avoid typos. I missed it because as you noted it works as it stands. I was however remiss in not changing the field name from that in my data file as this added some unwelcome confusion :( Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Where Graham has "date2" you need to put the name of the field in the > data source that you want to compare with (i.e. that has the date in [quoted text clipped - 78 lines] >> Message posted via OfficeKB.com >> http://www.officekb.com/Uwe/Forums.aspx/word-mailmerge/200802/1 Well, I suppose saying in "theory" is not quite right either given that there is no formal specificaiton of the field language or its behaviour :-) The other thing that might be causing problems here is that, regardless of how the DATE field nested in the ASK field is formatted, the user will need to enter the date in the format set up in their regional options (or any other format where the month and day are not ambiguous. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Re the typo - I use the macro at http://www.gmayor.com/export_field.htm to > export field contructions to avoid typos. I missed it because as you noted [quoted text clipped - 83 lines] >>> Message posted via OfficeKB.com >>> http://www.officekb.com/Uwe/Forums.aspx/word-mailmerge/200802/1 The OP did say earlier in the thread that he was using UK pattern dates. Just to clarify my original suggestion using the field name that was adopted earlier in the thread, and restoring the missing quote ;) { ASK MyDate "Start Date" \d { Date \@ "dd/MM/yyyy"} \o }{ SKIPIF { MERGEFIELD DateField } <> "{ REF MyDate \@ "d " \*Ordinal}{ REF MyDate \@ " MMMM yyyy" }" } This should work provided (as Peter indicated) {Mergefield DateField} produces a date in the exact format 1st January 2008 ie 1[space]st[space]January[space]2008 If there is no space between 1 and st, change { REF MyDate \@ "d " \*Ordinal} to {REF MyDate \@ "d" \*Ordinal} If you have some other format but January eg january or JANUARY you will need more formatting switches. The match must be a true match. http://www.gmayor.com/formatting_word_fields.htm Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Well, I suppose saying in "theory" is not quite right either given > that there is no formal specification of the field language or its [quoted text clipped - 99 lines] >>>> Message posted via OfficeKB.com >>>> http://www.officekb.com/Uwe/Forums.aspx/word-mailmerge/200802/1 Yes, this works fine, and I think that I (just about) understand HOW it works. So now I have 2 solutions to my orignal query. Gentlemen, many thanks indeed for sparing the time to sort this out. We now have a 'system' that is comfortable to use and more than adequately meets our needs. It's just great >The OP did say earlier in the thread that he was using UK pattern dates. > [quoted text clipped - 25 lines] >>>>> Message posted via OfficeKB.com >>>>> http://www.officekb.com/Uwe/Forums.aspx/word-mailmerge/200802/1 You are welcome :) Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Yes, this works fine, and I think that I (just about) understand HOW > it works. So now I have 2 solutions to my orignal query. Gentlemen, [quoted text clipped - 32 lines] >>>>>> Message posted via OfficeKB.com >>>>>> http://www.officekb.com/Uwe/Forums.aspx/word-mailmerge/200802/1 Many thanks again for your further input. It is much appreciated. Please also see my final post to Graham Mayor. >Where Graham has "date2" you need to put the name of the field in the data >source that you want to compare with (i.e. that has the date in "1st [quoted text clipped - 32 lines] >>>>>>>> -- >>>>>>>> Message posted via http://www.officekb.com I tried this and it works just fine. Many thanks indeed for your help. It is much appreciated. Graham Mayor also came up with a solution which I couldn't make work. Please see my reply to his last input. >OK, it certainly works here, i.e. in principle, so maybe the detail needs >checking: [quoted text clipped - 23 lines] >>>>>>>> >>>>>>>> Many thanks in anticipation. You need to consider whether mail merge is really the right tool for this job. Maybe something more along the lines of the method contained in the last of the following series of articles is more appropriate Please Fill Out This Form Part 1: Create professional looking forms in Word http://www.computorcompanion.com/LPMArticle.asp?ID=22 Part 2: Adding Automation to your Word forms. http://www.computorcompanion.com/LPMArticle.asp?ID=46 Part 3: Learn more VBA (macros) to automate your forms. http://www.computorcompanion.com/LPMArticle.asp?ID=119 Part 4: Use custom dialog boxes in your Word forms http://www.computorcompanion.com/LPMArticle.asp?ID=127 Part 5: Connect your AutoForm to a database to save input time and keep better records! http://www.computorcompanion.com/LPMArticle.asp?ID=136 SignatureHope 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 > Thanks for the reply. > [quoted text clipped - 24 lines] >>> >>> Many thanks in anticipation. Doug This is very much the advanced course for me. It will take me some time to get my head around this technique, but no doubt, it has considrable advantages over mail merge. I just need to spend the time working on it. However, many thanks for taking the time to advise. It is much appreciated. >You need to consider whether mail merge is really the right tool for this >job. [quoted text clipped - 24 lines] >>>> >>>> Many thanks in anticipation. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.