Set up a function in a MS word letter to add days to system date?

[Ross]

I am trying to set up a function in a MS Word mail merge letter that adds 14
days to the system date (e.g to tell the addressee when to respond by). I
have tried the "DateAdd" function and it doesn't seem to work as well as
taking the system date and trying to add 14 to it. Help!

 

[Jezebel]

Adding 14 is all DateAdd will do anyway. Dates are just numbers: the integer
part counts days. Now+14 is exactly the same as DateAdd("d", 14, Now)

 

[Ross]

Thanks Jezebel- I agree with what you say, but I guess the problem is how to
make "DateAdd" work in the body of the letter. I have been using the insert
command from the top toolbar in MS Word for "date/time", selecting the
format, etc. Then I press "ALT F9" and try to add the DateAdd function using
the inserted infromation as the "Now" information in your example. All I ever
get when I do this (and I have tried several combinations of = signs, ('s,
['s etc.) is the same system date or in some cases a blank.

Help!

 

[Jezebel]

Yes, there's no built-in way to do date arithmetic using fields. It *can* be
done (after a fashion - it involves a spectacular congeries of nested fields
to convert the current date to Julian number, do the arithmetic, and convert
back). Don't go there.

The simpler (and reliable!) method, if you're doing a mailmerge, is to
include the date you want in the mailmerge fields. If you're using SQL, you
can simply add it as another field to the SQL statement - "SELECT ....
(Now() + 14) as DueDate ...." then use <<DueDate>> in the body of the
document.

Thanks Jezebel- I agree with what you say, but I guess the problem is how
to
make "DateAdd" work in the body of the letter. I have been using the
insert
command from the top toolbar in MS Word for "date/time", selecting the
format, etc. Then I press "ALT F9" and try to add the DateAdd function
using
the inserted infromation as the "Now" information in your example. All I
ever
get when I do this (and I have tried several combinations of = signs, ('s,
['s etc.) is the same system date or in some cases a blank.

Help!

Adding 14 is all DateAdd will do anyway. Dates are just numbers: the
integer
part counts days. Now+14 is exactly the same as DateAdd("d", 14, Now)

 

[macropod]

Hi Ross,

To see how to add a given number of days to a date in a mailmerge, plus just
about everything else you might want to do with dates in Word using field
coding instead of macros, check out my Date Calc 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902

In particular, check out the entry under the heading "DATE CALCULATIONS IN A
MAILMERGE"

Cheers

 

[Jean-Guy Marcil]

Jezebel was telling us:
Jezebel nous racontait que :

Yes, there's no built-in way to do date arithmetic using fields. It
*can* be done (after a fashion - it involves a spectacular congeries
of nested fields to convert the current date to Julian number, do the
arithmetic, and convert back). Don't go there.

Wait a second or two... macropod will be along...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jezebel]

Yeah, I was hoping to stay away from that nonsense, but sure enough ...

 

[macropod]

No need to wait ... see my other post!

 

[Jezebel]

yes Mac, we've seen. We were just hoping you'd grown out if it.

No need to wait ... see my other post!

--
macropod
[MVP - Microsoft Word]

Jezebel was telling us:
Jezebel nous racontait que :


Wait a second or two... macropod will be along...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jezebel]

We've had this discussion too many times to make it worth re-hashing. As you
are well aware, I think it is irresponsible of you to promote your
"solutions" for serious use. I have seen too much damage done to respectable
companies, from trying to implement jejune "code" like yours for serious
commercial use, to have any patience at all your efforts to foist this stuff
on users who may -- given the context of this forum -- be misled into
thinking that they can rely on it.


And I shall go on doing everything I can to warn users away from what you
offer.

Jezebel, what is your problem?

Don't you have anything better to do than deride a perfectly workable
solution other than your own?

--
macropod
[MVP - Microsoft Word]

yes Mac, we've seen. We were just hoping you'd grown out if it.

No need to wait ... see my other post!

--
macropod
[MVP - Microsoft Word]


Jezebel was telling us:
Jezebel nous racontait que :

Yes, there's no built-in way to do date arithmetic using fields. It
*can* be done (after a fashion - it involves a spectacular congeries
of nested fields to convert the current date to Julian number, do
the
arithmetic, and convert back). Don't go there.

Wait a second or two... macropod will be along...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[macropod]

OK, so it really comes down to nothing more than you being prejudiced. Quite
dishonest of you, then, to make out on that basis that such solutions can't
be relied on. Many thousands of other Word users have found the field-based
solutions to be completely reliable. You've yet to show a single instance of
them failing.

--
macropod
[MVP - Microsoft Word]

We've had this discussion too many times to make it worth re-hashing. As you
are well aware, I think it is irresponsible of you to promote your
"solutions" for serious use. I have seen too much damage done to respectable
companies, from trying to implement jejune "code" like yours for serious
commercial use, to have any patience at all your efforts to foist this stuff
on users who may -- given the context of this forum -- be misled into
thinking that they can rely on it.


And I shall go on doing everything I can to warn users away from what you
offer.

Jezebel, what is your problem?

Don't you have anything better to do than deride a perfectly workable
solution other than your own?

--
macropod
[MVP - Microsoft Word]

yes Mac, we've seen. We were just hoping you'd grown out if it.



No need to wait ... see my other post!

--
macropod
[MVP - Microsoft Word]


Jezebel was telling us:
Jezebel nous racontait que :

Yes, there's no built-in way to do date arithmetic using fields. It
*can* be done (after a fashion - it involves a spectacular congeries
of nested fields to convert the current date to Julian number, do
the
arithmetic, and convert back). Don't go there.

Wait a second or two... macropod will be along...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jezebel]

I'm not decrying fields in general; only your abuse of them.

But I'm not going to explain the problem to you *yet again*. (Your
comprehension difficulties are, I suspect, part of what challenges you.)
It's not prejudice to dismiss someone who claims a competence patently
beyond their ability. The sentiment in this case is outright CONTEMPT.

OK, so it really comes down to nothing more than you being prejudiced.
Quite
dishonest of you, then, to make out on that basis that such solutions
can't
be relied on. Many thousands of other Word users have found the
field-based
solutions to be completely reliable. You've yet to show a single instance
of
them failing.

--
macropod
[MVP - Microsoft Word]

We've had this discussion too many times to make it worth re-hashing. As you
are well aware, I think it is irresponsible of you to promote your
"solutions" for serious use. I have seen too much damage done to respectable
companies, from trying to implement jejune "code" like yours for serious
commercial use, to have any patience at all your efforts to foist this stuff
on users who may -- given the context of this forum -- be misled into
thinking that they can rely on it.


And I shall go on doing everything I can to warn users away from what you
offer.

Jezebel, what is your problem?

Don't you have anything better to do than deride a perfectly workable
solution other than your own?

--
macropod
[MVP - Microsoft Word]


yes Mac, we've seen. We were just hoping you'd grown out if it.



No need to wait ... see my other post!

--
macropod
[MVP - Microsoft Word]


Jezebel was telling us:
Jezebel nous racontait que :

Yes, there's no built-in way to do date arithmetic using fields. It
*can* be done (after a fashion - it involves a spectacular congeries
of nested fields to convert the current date to Julian number, do
the
arithmetic, and convert back). Don't go there.

Wait a second or two... macropod will be along...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Graham Mayor]

You can add me to those with comprehension difficulties, because I too fail
to see what difference a calculation in vba
has from a calculation in a field - and I too have been following the
various discussions on the subject. If the field gives the correct result,
then what is the problem? Despite your repeated ascertions that the
calculated fields are unreliable and give inaccurate results, you still
haven't produced a single example of real evidence to back up your
prejudice. Abuse and contempt from behind a mask of anonimity don't cut it
as arguments to support your position.

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


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

I'm not decrying fields in general; only your abuse of them.

But I'm not going to explain the problem to you *yet again*. (Your
comprehension difficulties are, I suspect, part of what challenges
you.) It's not prejudice to dismiss someone who claims a competence
patently beyond their ability. The sentiment in this case is outright
CONTEMPT.

OK, so it really comes down to nothing more than you being
prejudiced. Quite
dishonest of you, then, to make out on that basis that such solutions
can't
be relied on. Many thousands of other Word users have found the
field-based
solutions to be completely reliable. You've yet to show a single
instance of
them failing.

--
macropod
[MVP - Microsoft Word]

We've had this discussion too many times to make it worth
re-hashing. As you are well aware, I think it is irresponsible of
you to promote your "solutions" for serious use. I have seen too
much damage done to respectable companies, from trying to implement
jejune "code" like yours for serious commercial use, to have any
patience at all your efforts to foist this stuff on users who may
-- given the context of this forum -- be misled into thinking that
they can rely on it. And I shall go on doing everything I can to warn
users away from
what you offer.





Jezebel, what is your problem?

Don't you have anything better to do than deride a perfectly
workable solution other than your own?

--
macropod
[MVP - Microsoft Word]


yes Mac, we've seen. We were just hoping you'd grown out if it.



No need to wait ... see my other post!

--
macropod
[MVP - Microsoft Word]


Jezebel was telling us:
Jezebel nous racontait que :

Yes, there's no built-in way to do date arithmetic using
fields. It *can* be done (after a fashion - it involves a
spectacular congeries of nested fields to convert the current
date to Julian number, do the
arithmetic, and convert back). Don't go there.

Wait a second or two... macropod will be along...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Jezebel]

Oh Graham, gotta love those one-true-scotsman arguments.Unlike Macropod, I
think you've been around corporate IT setups long enough to have seen the
damage done by feral programming. You try testing Macropods field bonanzas
with the full spread of calendar settings, regional date formats, and
invalid data entry (such as you *must* cater for in any real-world
application) and you'll see the problem immediately and explicitly. And I
say *you* try it, because Macropod clearly hasn't.

As a simple example, his "Interactively Calculate A Past Or Future Date"
code happily produces outputs like

If the starting date is 99/99/99 and the offset is 0 days, then the new date
is 04-77-9999.

OK, so the data entry was a mistake (users make mistakes in my part of the
world - not in yours?); but the field code doesn't say so, and in any case
what the hell is month '77' ?


I've never disputed that these codes *can* produce the right answers. My
point has always been, quite specifically, that it is irresponsible to rely
on this kind of pseudo-software for real-world commercial purposes.

You can add me to those with comprehension difficulties, because I too
fail to see what difference a calculation in vba
has from a calculation in a field - and I too have been following the
various discussions on the subject. If the field gives the correct result,
then what is the problem? Despite your repeated ascertions that the
calculated fields are unreliable and give inaccurate results, you still
haven't produced a single example of real evidence to back up your
prejudice. Abuse and contempt from behind a mask of anonimity don't cut it
as arguments to support your position.

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


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

I'm not decrying fields in general; only your abuse of them.

But I'm not going to explain the problem to you *yet again*. (Your
comprehension difficulties are, I suspect, part of what challenges
you.) It's not prejudice to dismiss someone who claims a competence
patently beyond their ability. The sentiment in this case is outright
CONTEMPT.

OK, so it really comes down to nothing more than you being
prejudiced. Quite
dishonest of you, then, to make out on that basis that such solutions
can't
be relied on. Many thousands of other Word users have found the
field-based
solutions to be completely reliable. You've yet to show a single
instance of
them failing.

--
macropod
[MVP - Microsoft Word]


We've had this discussion too many times to make it worth
re-hashing. As you are well aware, I think it is irresponsible of
you to promote your "solutions" for serious use. I have seen too
much damage done to respectable companies, from trying to implement
jejune "code" like yours for serious commercial use, to have any
patience at all your efforts to foist this stuff on users who may
-- given the context of this forum -- be misled into thinking that
they can rely on it. And I shall go on doing everything I can to warn
users away from
what you offer.





Jezebel, what is your problem?

Don't you have anything better to do than deride a perfectly
workable solution other than your own?

--
macropod
[MVP - Microsoft Word]


yes Mac, we've seen. We were just hoping you'd grown out if it.



No need to wait ... see my other post!

--
macropod
[MVP - Microsoft Word]


Jezebel was telling us:
Jezebel nous racontait que :

Yes, there's no built-in way to do date arithmetic using
fields. It *can* be done (after a fashion - it involves a
spectacular congeries of nested fields to convert the current
date to Julian number, do the
arithmetic, and convert back). Don't go there.

Wait a second or two... macropod will be along...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[macropod]

what the hell is month '77'?
C'mon Jezebel, is that the best you can do? I would have thought anyone
stupid enough to think '99' was a day or month would know. Since when is
'99/99/99' a date? So, you input garbage, ignoring the field's prompt to
enter "the starting date, in dd/mm/yyyy format" and got garbage back. Wow.
What did you expect? How about posting an example of an error using *real*
dates?

Granted, I didn't include any error checking, but then neither does much of
the VBA code you post in this and other NGs. By your standards that makes
VBA unreliable, or at least many of *your* implementations of it. If all it
takes to satisfy you that field-based date calculations are reliable is for
error-checking to be included, where appropriate, I'd be happy to oblige -
I'll add it to the next release. Given your obvious prejudices, however, I
somehow doubt that any field-based solution will satisfy you, no matter how
robust.

For the OP's purposes, the data validation should exist at the time of entry
into the db/table being used for the mailmerge source, so modifying the
existing field codes for the mailmerge example is completely unnecessary.

As for your claim that you've "never disputed that these codes *can* produce
the right answers", aren't you the Jezebel who previously claimed in these
NGs that:
a) "Word can't do arithmetic with dates (unlike every other Office app)"
(Sep 11 2004).
b) all Julian number formulae have "*extremely* dubious reliability" (Oct 9
2004), i.e. not just my implementation of them;
b) "Calculations are limited to very limited calculations like SUM(ABOVE)"
(Aug 9 2005). Note: the bit between the #s is added for context.
c) "Word has nothing like the SUM() capabilities of Excel. Nor are cell
references so simple" (Feb 7 2006).
Perhaps you should re-read what you're own posts on this topic.

As I said once before, it's a matter of horses for courses. A big plus for
the field-based solution is that it doesn't raise the same security issues
you get with macros.

 

[Jezebel]

OK, to spell it out for you, yet again --

1. Your code doesn't recognise, and cannot handle invalid input. Even
obvious problems like NULL or zero delivered by a database, or a date
entered in US format rather than UK, or simple typos like '31 April' will
bring your code asunder. Faulty data is a fact of life: code has to deal
with it. Blaming the operator for 'ignoring the prompt' is just asinine.
People make mistakes, and they don't always notice them. If you install
software that calls for dates in UK format, in a corporation with mixed
US/UK personnel, people are going to get it wrong some of the time.

2. Your code is not guaranteed to return a valid date. (As demonstrated by
your month 77.) Good code will return either a date or an error: not garbage
as in your case.

3. Your code has no protection against inadvertent changes to the formulas.
As you must have noticed elsewhere in these forums, there is no shortage of
people who accidentally press alt-f9 and wonder what's gone wrong with their
document; and there are plenty of people who seem to have problems with
their touchpad and find themselves typing in the wrong part of the document.
It's not hard to see these problems coming together to result in an
unnoticed digit added to or removed from one of your formula constants.

4. Your code has not been tested across the range of date and calendar
settings.

5. Your formula implementations do not meet any standards of verifiability,
and specifically, run foul of the IT governance standards that are now legal
requirements for listed companies in most of the world.


We've discussed all this before: you think these problems don't matter. I
do.