MS Office Forum / Word / Programming / October 2006
Set up a function in a MS word letter to add days to system date?
|
|
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!
|
|
|