 |
 | 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 2007Using date fields in merge
HI, I am merging data from Access database to print mailing labels. If i want to print only those addresses with date less than 3 days from today, what criteria can i give in the mail merge query options? I simply tried Date() - 3 but that doesnt work. Also i do not want to send to records where a logical field is TRUE (like a Do not mail flag). Thanks for any inputs Ramesh Use a Query in Access to filter your data. Data manipulation is best done in a database than in a word processor.  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 > HI, > [quoted text clipped - 9 lines] > Thanks for any inputs > Ramesh While I agree with Doug that it is better to do this in Access, it can be done in Word. In order to compare dates you have to be careful about the formatting switches you apply - see the example at http://www.gmayor.com/formatting_word_fields.htm Calculation with dates in fields to get the date -3 is altogether more complicated and for that you need to see the examples at www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902 You can use the example to get a future date using a minus delay of 3 then set the switch to "yyyyMMdd" before comparing with the field date. Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > HI, > [quoted text clipped - 9 lines] > Thanks for any inputs > Ramesh thanks much Graham and Doug. that was useful. though i am now looking for some simpler ways to do this rather than the complicated functions. Ramesh > While I agree with Doug that it is better to do this in Access, it can be > done in Word. In order to compare dates you have to be careful about the [quoted text clipped - 20 lines] >> Thanks for any inputs >> Ramesh If it was simple we would have told you! Based on what I posted earlier, if you start your merge document with the following field { SKIPIF{ Mergefield MyDate \@ "yyyyMMdd"} < "{QUOTE{SET Delay -3}{SET a{=INT((14-{DATE \@ M})/12)}}{SET b{={DATE \@ yyyy}+4800-a}}{SET c{={DATE \@ M}+12*a-3}}{SET d{DATE \@ d}}{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}{SET e{=INT((4*(jd+32044)+3)/146097)}}{SET f{=jd+32044-INT(146097*e/4)}}{SET g{=INT((4*f+3)/1461)}}{SET h{=f-INT(1461*g/4)}}{SET i{=INT((5*h+2)/153)}}{SET dd{=h-INT((153*i+2)/5)+1}}{SET mm{=i+3-12*INT(i/10)}}{SET yy{=100*e+g-4800+INT(i/10)}}{QUOTE{=dd*10^6+mm*10^4+yy \# "00'-'00'-'0000"} \@ "yyyyMMdd" }}" } the merge will skip all the records with dates no more than three days old, but don't even try and enter this from the keyboard as you will get it wrong! Copy the essential part of the code from the link I posted earlier and add the condition and modify the switches. MyDate is the name of the field you are testing, so you'll need to change that to what you have in your data. Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > thanks much Graham and Doug. that was useful. though i am now > looking for some simpler ways to do this rather than the complicated [quoted text clipped - 33 lines] >>> Thanks for any inputs >>> Ramesh The simplest way will be to do it with a query in Access. Date arithmetic is quite simple in Access compared to Word. 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 much Graham and Doug. that was useful. though i am now looking > for some simpler ways to do this rather than the complicated functions. [quoted text clipped - 25 lines] >>> Thanks for any inputs >>> Ramesh But maybe not if someone has done the work for you ;) Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > The simplest way will be to do it with a query in Access. Date > arithmetic is quite simple in Access compared to Word. [quoted text clipped - 34 lines] >>>> Thanks for any inputs >>>> Ramesh Using Word fields to do date calculations is one thing, but how do you use them to exclude records in a label type merge (which is where this thread started)? Peter Jamieson > But maybe not if someone has done the work for you ;) > [quoted text clipped - 36 lines] >>>>> Thanks for any inputs >>>>> Ramesh Damn! I was forgetting that SKIPIF couldn't be used in a label merge :( Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Using Word fields to do date calculations is one thing, but how do > you use them to exclude records in a label type merge (which is where [quoted text clipped - 49 lines] >>>>>> Thanks for any inputs >>>>>> Ramesh Oh well, hoped there might be a way. It's a pity really - having even a slightly richer field language (OMITWHILE? OMITUNTIL? etc.) would solve a lot of these problems without people having to learn about VBA, Access queries, SQL, and latterly XML and all the other programming paraphernalia that tends to add to the general degree of difficulty of doing anything. It was looking at this problem that also reminded me that { DATABASE } fields, which can also do date calculations in a fairly straightforward way, won't work inside tables - even when the result is a scalar one... Peter Jamieson > Damn! I was forgetting that SKIPIF couldn't be used in a label merge :( > [quoted text clipped - 51 lines] >>>>>>> Thanks for any inputs >>>>>>> Ramesh It's a shame because the SkipIf does work well in a letter or directory merge (which is where I tested it). Signature<>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> > Oh well, hoped there might be a way. It's a pity really - having even > a slightly richer field language (OMITWHILE? OMITUNTIL? etc.) would [quoted text clipped - 70 lines] >>>>>>>> Thanks for any inputs >>>>>>>> Ramesh |
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.