| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using 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 |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
Use a Query in Access to filter your data. Data manipulation is best done
in a database than in a word processor. -- 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 "Ramesh" ramesh2020@gmaildotcom wrote in message ... 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 |
|
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
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 http://www.wopr.com/cgi-bin/w3t/show...?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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
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 "Graham Mayor" wrote in message ... 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 http://www.wopr.com/cgi-bin/w3t/show...?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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 |
|
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 "Graham Mayor" wrote in message ... 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 http://www.wopr.com/cgi-bin/w3t/show...?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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 |
|
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
The simplest way will be to do it with a query in Access. Date arithmetic
is quite simple in Access compared to Word. -- 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 "Ramesh" ramesh2020@gmaildotcom wrote in message ... 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 "Graham Mayor" wrote in message ... 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 http://www.wopr.com/cgi-bin/w3t/show...?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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 |
|
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
But maybe not if someone has done the work for you
 -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Doug Robbins - Word MVP wrote: The simplest way will be to do it with a query in Access. Date arithmetic is quite simple in Access compared to Word. "Ramesh" ramesh2020@gmaildotcom wrote in message ... 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 "Graham Mayor" wrote in message ... 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 http://www.wopr.com/cgi-bin/w3t/show...?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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 |
|
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
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 "Graham Mayor" wrote in message ... But maybe not if someone has done the work for you -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Doug Robbins - Word MVP wrote: The simplest way will be to do it with a query in Access. Date arithmetic is quite simple in Access compared to Word. "Ramesh" ramesh2020@gmaildotcom wrote in message ... 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 "Graham Mayor" wrote in message ... 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 http://www.wopr.com/cgi-bin/w3t/show...?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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 |
|
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
Damn! I was forgetting that SKIPIF couldn't be used in a label merge
 -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Peter Jamieson wrote: 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 "Graham Mayor" wrote in message ... But maybe not if someone has done the work for you -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Doug Robbins - Word MVP wrote: The simplest way will be to do it with a query in Access. Date arithmetic is quite simple in Access compared to Word. "Ramesh" ramesh2020@gmaildotcom wrote in message ... 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 "Graham Mayor" wrote in message ... 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 http://www.wopr.com/cgi-bin/w3t/show...?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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 |
|
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
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 "Graham Mayor" wrote in message ... Damn! I was forgetting that SKIPIF couldn't be used in a label merge -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Peter Jamieson wrote: 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 "Graham Mayor" wrote in message ... But maybe not if someone has done the work for you -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Doug Robbins - Word MVP wrote: The simplest way will be to do it with a query in Access. Date arithmetic is quite simple in Access compared to Word. "Ramesh" ramesh2020@gmaildotcom wrote in message ... 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 "Graham Mayor" wrote in message ... 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 http://www.wopr.com/cgi-bin/w3t/show...?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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 |
|
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Using date fields in merge
It's a shame because the SkipIf does work well in a letter or directory
merge (which is where I tested it). -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Peter Jamieson wrote: 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 "Graham Mayor" wrote in message ... Damn! I was forgetting that SKIPIF couldn't be used in a label merge --Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Peter Jamieson wrote: 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 "Graham Mayor" wrote in message ... But maybe not if someone has done the work for you -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Doug Robbins - Word MVP wrote: The simplest way will be to do it with a query in Access. Date arithmetic is quite simple in Access compared to Word. "Ramesh" ramesh2020@gmaildotcom wrote in message ... 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 "Graham Mayor" wrote in message ... 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 http://www.wopr.com/cgi-bin/w3t/show...?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. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Ramesh wrote: 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 |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Blank date fields in Excel merging with Date/Time | Mailmerge | |||
| Date Fields | Microsoft Word Help | |||
| How to get a date merge field to show a date 60 days in the future | Mailmerge | |||
| How do I format date and time fields in a merge document? | Mailmerge | |||
| Merge date fields | Mailmerge | |||
Linear Mode
