Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / October 2006

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ross - 26 Oct 2006 22:11 GMT
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 - 26 Oct 2006 22:15 GMT
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)

>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!
Ross - 26 Oct 2006 22:33 GMT
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)
[quoted text clipped - 4 lines]
> > 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 - 26 Oct 2006 23:27 GMT
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
[quoted text clipped - 22 lines]
>> > as
>> > taking the system date and trying to add 14 to it.  Help!
Jean-Guy Marcil - 27 Oct 2006 01:20 GMT
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...

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Jezebel - 27 Oct 2006 01:28 GMT
Yeah, I was hoping to stay away from that nonsense, but sure enough ...

> Jezebel was telling us:
> Jezebel nous racontait que :
[quoted text clipped - 5 lines]
>
> Wait a second or two... macropod will be along...
macropod - 27 Oct 2006 01:30 GMT
No need to wait ... see my other post!

Signature

macropod
[MVP - Microsoft Word]

> Jezebel was telling us:
> Jezebel nous racontait que :
[quoted text clipped - 5 lines]
>
> Wait a second or two... macropod will be along...
Jezebel - 27 Oct 2006 03:50 GMT
yes Mac, we've seen. We were just hoping you'd grown out if it.

> No need to wait ... see my other post!
>
[quoted text clipped - 7 lines]
>>
>> Wait a second or two... macropod will be along...
macropod - 27 Oct 2006 04:29 GMT
Jezebel, what is your problem?

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

Signature

macropod
[MVP - Microsoft Word]

> yes Mac, we've seen. We were just hoping you'd grown out if it.
>
[quoted text clipped - 9 lines]
> >>
> >> Wait a second or two... macropod will be along...
Jezebel - 27 Oct 2006 06:53 GMT
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?
>
[quoted text clipped - 15 lines]
>> >>
>> >> Wait a second or two... macropod will be along...
macropod - 27 Oct 2006 07:11 GMT
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.

Signature

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
[quoted text clipped - 26 lines]
> >> >>
> >> >> Wait a second or two... macropod will be along...
Jezebel - 27 Oct 2006 08:35 GMT
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
[quoted text clipped - 41 lines]
>> >> >>
>> >> >> Wait a second or two... macropod will be along...
Graham Mayor - 27 Oct 2006 09:01 GMT
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.

Signature

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> I'm not decrying fields in general; only your abuse of them.
>
[quoted text clipped - 63 lines]
>>>>>>> jmarcilREMOVE@CAPSsympatico.caTHISTOO
>>>>>>> Word MVP site: http://www.word.mvps.org
Jezebel - 27 Oct 2006 09:54 GMT
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
[quoted text clipped - 73 lines]
>>>>>>>> jmarcilREMOVE@CAPSsympatico.caTHISTOO
>>>>>>>> Word MVP site: http://www.word.mvps.org
macropod - 28 Oct 2006 08:43 GMT
> 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.

Signature

macropod
[MVP - Microsoft Word]

Jezebel - 28 Oct 2006 11:49 GMT
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.

>> what the hell is month '77'?
> C'mon Jezebel, is that the best you can do? I would have thought anyone
[quoted text clipped - 40 lines]
> the field-based solution is that it doesn't raise the same security issues
> you get with macros.
macropod - 27 Oct 2006 01:10 GMT
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

Signature

macropod
[MVP - Microsoft Word]

> 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!
 
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.