Using date fields in merge

[Ramesh]

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

 

[Doug Robbins - Word MVP]

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

 

[Graham Mayor]

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.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[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

 

[Graham Mayor]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Doug Robbins - Word MVP]

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

 

[Graham Mayor]

But maybe not if someone has done the work for you

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Peter Jamieson]

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]

Damn! I was forgetting that SKIPIF couldn't be used in a label merge

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Peter Jamieson]

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]

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>