Using DateDiff in Formfields...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Foley - 06 Dec 2006 14:31 GMT

Hey Gang,

Word 2003

Trying to create an automated form for my church and am stumped as to how to
do this. I have a calendar form (actually two of them) that populate
formfields (StartDate and EndDate). I have another formfield (TotalDays)
that I cm trying to populate with the number of days difference between the
other two formfields. I assigned the following macro to the "Entry" of the
TotalDays field. However, it is not working.

Sub CalculateTotalDays()
ActiveDocument.FormFields("TotalDays").Result = DateDiff("d", EndDate,
StartDate)
End Sub

I am assuming that Word can't recognize formfields the same way Acces does
to know to use them in the calculation, but I can't seem to find a way to
make it work.

Any ideas? TIA!

Signature

Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com

Reply to this Message

Graham Mayor - 06 Dec 2006 14:57 GMT

If you don't mind Jezebel's unwarranted scorn, you could do this with a Word
field assembly as opposed to a form field for the result. The construction
is :

{={SET a{=INT((14-{EndDate \@ M})/12)}}{SET b{={EndDate \@
yyyy}+4800-a}}{SET c{={EndDate \@ M}+12*a-3}}{SET d{EndDate \@
d}}{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}-{SET
a{=INT((14-{StartDate \@ M})/12)}}{SET b{={StartDate \@ yyyy}+4800-a}}{SET
c{={StartDate \@ M}+12*a-3}}{SET d{StartDate \@
d}}{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}\# ,0}

www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902 will give you the
source document containing the fields to avoid trying to reconstruct from
here. Don't forget to check the calculate on exit check boxes of the two
date source fields.

Signature

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

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

> Hey Gang,
>
[quoted text clipped - 17 lines]
>
> Any ideas? TIA!

Reply to this Message

Bill Foley - 06 Dec 2006 15:41 GMT

Thanks, Graham. I'll give it a go. I'll also hang around for the "correct"
way from Jezebel! HA!

Signature

Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com

> If you don't mind Jezebel's unwarranted scorn, you could do this with a
> Word field assembly as opposed to a form field for the result. The
[quoted text clipped - 33 lines]
>>
>> Any ideas? TIA!

Reply to this Message

Greg Maxey - 06 Dec 2006 15:59 GMT

Bill,

I won't enter the fray and say which is more correct (I don't know). I
did it with a macro like this:
Sub CalculateTotalDays()
Dim oFF As FormFields
Dim EndDate As Date
Dim StartDate As Date
Set oFF = ActiveDocument.FormFields
EndDate = oFF("EndDate").Result
StartDate = oFF("StartDate").Result
oFF("TotalDays").Result = DateDiff("d", EndDate, StartDate)
End Sub

> Thanks, Graham. I'll give it a go. I'll also hang around for the "correct"
> way from Jezebel! HA!
[quoted text clipped - 49 lines]
> >>
> >> Any ideas? TIA!

Reply to this Message

Bill Foley - 06 Dec 2006 16:13 GMT

Works a peach! THANKS!

Signature

Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com

> Bill,
>
[quoted text clipped - 69 lines]
>> >>
>> >> Any ideas? TIA!

Reply to this Message

Bill Foley - 06 Dec 2006 16:14 GMT

Kept getting some sort of "syntax error" so I gave into the other way. Must
not be smart enough to use the "official field" method! HA!

THANKS!

Signature

Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com

> If you don't mind Jezebel's unwarranted scorn, you could do this with a
> Word field assembly as opposed to a form field for the result. The
[quoted text clipped - 33 lines]
>>
>> Any ideas? TIA!

Reply to this Message

Graham Mayor - 07 Dec 2006 06:10 GMT

Curious - as the default settings in the master document even have the same
field names. It works for me straight out of the box by simply cutting and
pasting the code. the problem may have been that of having the calculate on
exit checkbox checked on both fields as I suggested, rather than on the
second only as there would be no date in the second field when starting and
that would confuse it :)

However our friend Jezebel will no doubt have a wry smile on his face ;)

Signature

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

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

> Kept getting some sort of "syntax error" so I gave into the other
> way. Must not be smart enough to use the "official field" method! HA!
[quoted text clipped - 44 lines]
>>>
>>> Any ideas? TIA!

Reply to this Message

macropod - 07 Dec 2006 07:34 GMT

Hi Bill,

If you used the field coding Graham posted, you need to ensure all the field
braces (i.e. '{ }') are created in pairs via Ctrl-F9. Simply typing them
won't work and will produce syntax errors just like you described.

Cheers

Signature

macropod
[MVP - Microsoft Word]

> Kept getting some sort of "syntax error" so I gave into the other way. Must
> not be smart enough to use the "official field" method! HA!
[quoted text clipped - 38 lines]
> >>
> >> Any ideas? TIA!

Reply to this Message

Graham Mayor - 07 Dec 2006 10:10 GMT

I did suggest he pasted the original from your source document which will
work as supplied - even to the correct field names.

Signature

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

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

> Hi Bill,
>
[quoted text clipped - 62 lines]
>>>>
>>>> Any ideas? TIA!

Reply to this Message

macropod - 07 Dec 2006 11:43 GMT

Hi Graham,

Yes, I noticed that. Not doing so and messing up the coding is the only
reason I can think of for the syntax error.

Cheers

Signature

macropod
[MVP - Microsoft Word]

> I did suggest he pasted the original from your source document which will
> work as supplied - even to the correct field names.
[quoted text clipped - 25 lines]
> >>> {={SET a{=INT((14-{EndDate \@ M})/12)}}{SET b{={EndDate \@
> >>> yyyy}+4800-a}}{SET c{={EndDate \@ M}+12*a-3}}{SET d{EndDate \@

d}}{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}-{SET

> >>> a{=INT((14-{StartDate \@ M})/12)}}{SET b{={StartDate \@
> >>> yyyy}+4800-a}}{SET c{={StartDate \@ M}+12*a-3}}{SET d{StartDate \@
[quoted text clipped - 35 lines]
> >>>>
> >>>> Any ideas? TIA!

Reply to this Message

Doug Robbins - Word MVP - 06 Dec 2006 15:56 GMT

ActiveDocument.FormFields("TotalDays").Result = DateDiff("d", EndDate,
StartDate)

Should be:

ActiveDocument.FormFields("TotalDays").Result = DateDiff("d",
ActiveDocument.FormFields("EndDate").Result,
ActiveDocument.FormFields("StartDate").Result)

Signature

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

> Hey Gang,
>
[quoted text clipped - 17 lines]
>
> Any ideas? TIA!

Reply to this Message

Bill Foley - 06 Dec 2006 16:13 GMT

Kinda what I figured. Got it working now. THANKS!

Signature

Bill Foley
Microsoft PowerPoint MVP
Microsoft Office Specialist Master Instructor
www.pttinc.com

> ActiveDocument.FormFields("TotalDays").Result = DateDiff("d", EndDate,
> StartDate)
[quoted text clipped - 26 lines]
>>
>> Any ideas? TIA!

Reply to this Message