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.

Request help with a custom date field...

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
charlie6067 - 11 Oct 2006 18:17 GMT
I've read through many posts and other articles on calculating date
fields but can't find an answer to this specific use. I'm creating a
legal agreement where the user types the effective date in a field.
There is another place in the document that needs to read the effective
date field and add 2 years to it and then subtract 1 day (7/15/2006 =
7/14/2008). I had planned to use a simple message box to explain how
the user can manually calculate this forward date but want to know if
this date can be automatically calculated for them? I know that Word's
{Date} fields can be a bit hard to work with but wonder if VBA is a
better route, and how to code it?

Many thanks,
Charlie
charlie6067
Greg Maxey - 11 Oct 2006 18:47 GMT
You could use DateAdd to add 2 years then subtract 1 day:

Sub DateForward()
Dim StartDate As Date
Dim IntDate As Date
Dim EndDate As Date
StartDate = Date
IntDate = DateAdd("yyyy", 2, StartDate)
EndDate = DateAdd("d", -1, IntDate)
MsgBox EndDate
End Sub

> I've read through many posts and other articles on calculating date
> fields but can't find an answer to this specific use. I'm creating a
[quoted text clipped - 10 lines]
> Charlie
> charlie6067
charlie6067 - 11 Oct 2006 19:35 GMT
Greg,

Many thanks again for your great help and quick response.

Charlie
charlie6067

> You could use DateAdd to add 2 years then subtract 1 day:
>
[quoted text clipped - 22 lines]
> > Charlie
> > charlie6067
Dave Lett - 11 Oct 2006 19:05 GMT
Hi Charlie,

Here's what I have in my document:

Effective date: 10/11/2006
Calculated date: 10/10/2008

There are a total of eight fields. The effective date has three fields: {
Date \@ "MM" }/{ Date \@ "dd" }/{ Date \@ "yyyy" }
I think bookmarked each field as Month, Day, Year respectively.

The calculated date has five fields (2 nested inside of three): { = { Ref
Month } } / { = { Ref Day } - 1}/ { = { Ref Year } +2 }

You could do something similar in your document.

HTH,
Dave

> I've read through many posts and other articles on calculating date
> fields but can't find an answer to this specific use. I'm creating a
[quoted text clipped - 10 lines]
> Charlie
> charlie6067
Greg Maxey - 11 Oct 2006 19:18 GMT
Dave,

What about the first day of the month?  ;-)

> Hi Charlie,
>
[quoted text clipped - 29 lines]
> > Charlie
> > charlie6067
Dave Lett - 11 Oct 2006 20:08 GMT
Hi Greg,

Good point; I vote that we take the first day of every month off. Uh, I'm
sure that we could nest some if fields to make this happen, don't you think?

> Dave,
>
[quoted text clipped - 33 lines]
>> > Charlie
>> > charlie6067
Greg Maxey - 11 Oct 2006 20:11 GMT
Dave,

Yep to both.

> Hi Greg,
>
[quoted text clipped - 38 lines]
> >> > Charlie
> >> > charlie6067
Peter Jamieson - 13 Oct 2006 09:35 GMT
The user may also need to check with the legal department what happens if
the date is 29th February - e.g. if it's 2000-02-29 is "2 years minus a day"
2002-02-28 or 2002-02-27? I expect there is a "right answer" but the point
is that when you use Dateadd to add 2 years you will get 2002-02-28, so if
the right answer is 2002-02-28 in that situation you do not want to subtract
a day.

Peter Jamieson

> Dave,
>
[quoted text clipped - 33 lines]
>> > Charlie
>> > charlie6067
charlie6067 - 11 Oct 2006 19:37 GMT
Hi Dave,

Many thanks for your quick reply and code. I'll try it out.
Charlie
charlie6067

> Hi Charlie,
>
[quoted text clipped - 29 lines]
> > Charlie
> > charlie6067
charlie6067 - 11 Oct 2006 21:50 GMT
Greg and Dave,

On this form, I just found out that our legal department wants the
agreement's effective date written as "...effective the [day] of
[month], [year]." When the user completes those three fields, how can
VBA convert them into a valid date that can be used for date
calculations later in the form, such as with the code you've provided?
I've looked at similar posts but can't seem to get the coding to not
use the system date. The code will look at the extract date and add 2
years minus 1 day. The above legal date may likely be later than the
system date when the form is completed. I'm sorry I didn't have all the
facts before making the first post.

Thanks,
Charlie
charlie6067

> Hi Dave,
>
[quoted text clipped - 35 lines]
> > > Charlie
> > > charlie6067
Greg Maxey - 11 Oct 2006 22:40 GMT
Charlie,

Let's say you have four formfields  bookmarked Day Suffix Month Year

Laid out like:  effective the Day Suffix of Month, Year.

The Suffix field is not enabled for fillin.

You have another field bookmarked "FinalDate" that is also not enabled for
fillin.

One exit from the Day field you could run something like:
Sub MakeSuffix()
Dim oFF As Word.FormFields
Set oFF = ActiveDocument.FormFields
If Not IsNumeric(oFF("Day").Result) Or oFF("Day").Result > 31 Or
oFF("Day").Result < 1 Then
 'Handle your error
End If
Select Case oFF("Day").Result
 Case Is = 1, 21, 31
   oFF("Suffix").Result = "st"
 Case Is = 2, 22
   oFF("Suffix").Result = "nd"
 Case Is = 3, 23
   oFF("Suffix").Result = "rd"
 Case Else
   oFF("Suffix").Result = "th"
End Select
End Sub

On exit from the Year field run something like:

Sub DateAddUp()
Dim InputDate As Date
Dim IDate As Date
Dim FinalDate As Date
Dim oFF As Word.FormFields
Dim myString As String
Set oFF = ActiveDocument.FormFields
myString = oFF("Day").Result & "/" & oFF("Month").Result & "/" &
oFF("Year").Result
InputDate = myString
IDate = DateAdd("yyyy", 2, InputDate)
oFF("FinalDate").Result = DateAdd("d", -1, IDate)
End Sub

Note.  You will probably want to include code that will alert the user if an
invalid date is entered or to ensure the fields are not left blank.  Either
will throw and error in the simplified code above.

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Greg and Dave,
>
[quoted text clipped - 52 lines]
>>>> Charlie
>>>> charlie6067
charlie6067 - 12 Oct 2006 15:08 GMT
Hi Greg,

A million thanks again for you continued help with my problem. I've
learned more from these forums than from the Help file or books
(although I am working my way through "VBA for Dummies." Whatever I
learn is shared with my forms team and it's also saved for future
reference. I'd like to give you a raise for helping me, but then after
taxes, you probably wouldn't have much left to spend - q=8^ ?

Thanks again!
Charlie
charlie6067

> Charlie,
>
[quoted text clipped - 109 lines]
> >>>> Charlie
> >>>> charlie6067
Greg Maxey - 12 Oct 2006 15:34 GMT
Well there is no tax on thanks yet!  Glad I can help.

> Hi Greg,
>
[quoted text clipped - 122 lines]
> > >>>> Charlie
> > >>>> charlie6067
charlie6067 - 12 Oct 2006 16:37 GMT
Hi Greg,

The MakeSuffix code works great and I added a message box to alert
users if a number larger than 31 is typed in the Day field. I can add
this to several other forms.

But, when I pasted the DateAddUp code in VBA, one line of code was
immediately flagged in red (using Word 97). When I ran the code I got a
"Compile error: Syntax error. "

       myString = oFF("Day").Result & "/" & oFF("Month").Result & "/"
&

myString and oFF were declared so I don't know why it's being flagged?
Is it due to my version of VBA or how can I fix this?

Also, what does   & "/" &    mean in the code?

As always, thanks again,
Charlie
charlie6067

> Well there is no tax on thanks yet!  Glad I can help.
>
[quoted text clipped - 124 lines]
> > > >>>> Charlie
> > > >>>> charlie6067
Greg Maxey - 12 Oct 2006 17:20 GMT
Charlie,

Glad that part is is working.

When you paste code in these newsgroups the lines are sometimes
wrapped.

myString = oFF("Day").Result & "/" & oFF("Month").Result & "/" &
oFF("Year").Result

Should all appear on one line.  I think that will resolve your issue.

<Also, what does   & "/" &    mean in the code?

If I type 12 October 2006 in the fields then I want the string to be
built as:

12/October/2006.  (Thinking about it now I don't suppose that is really
necessary).

HTH

> Hi Greg,
>
[quoted text clipped - 146 lines]
> > > > >>>> Charlie
> > > > >>>> charlie6067
charlie6067 - 12 Oct 2006 18:22 GMT
Greg,

That eliminated the code in red. But, when I ran the form the same line
of code errored out with the message "Object variable or With block
variable not set". I read the help file but couldn't diagnose the
problem. If you know a quick fix, please let me know. Otherwise, that's
okay because I still learned a lot from your postings. Thanks for your
help today and yesterday!

Charlie
charlie6067

> Charlie,
>
[quoted text clipped - 168 lines]
> > > > > >>>> Charlie
> > > > > >>>> charlie6067
Greg Maxey - 12 Oct 2006 20:27 GMT
Charlie,

It works fine here with Word2000 and 2003.  I don't have Word97.

You might try posting that code as a new question and someone with Word97
may be able to provide and answer.

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Greg,
>
[quoted text clipped - 206 lines]
>> > > > > >>>> Charlie
>> > > > > >>>> charlie6067
charlie6067 - 12 Oct 2006 22:11 GMT
Greg,

Thanks for verifying the versions. My company has users on all three
versions which make designing templates with VBA a challenge. Excellent
suggestion and I'll repost.

Thanks again for all your help with this,
Charlie
charlie6067

> Charlie,
>
[quoted text clipped - 219 lines]
> >> > > > > >>>> Charlie
> >> > > > > >>>> charlie6067
Peter Jamieson - 13 Oct 2006 10:08 GMT
The same code works OK here in Word 97: could you have run it against a
document that did not have any FormFields?

Peter Jamieson

> Greg,
>
[quoted text clipped - 253 lines]
>> >> > > > > >>>> Charlie
>> >> > > > > >>>> charlie6067
charlie6067 - 13 Oct 2006 19:48 GMT
Hi Peter,

Many thanks for the quick reply. I'm glad it's running on your Word 97
- that's encouraging. Would it be possible to e-mail me your file and I
can analyze it here? My e-mail is charlie6067@yahoo.com (or not sure if
files can be attached to postings in this group?). Or, the VBA from the
template (code from Greg) is listed below. It looks like he declared
all the variables so I'm not sure why I can't run it? Maybe I copied
something different than what Greg had and that's causing a problem?
The error message displays when I tab out of the Year field at the
beginning of the form. I'll run the form on Word 2003 and see how that
goes.

Noticed you're in the UK. I have a daughter studying her junior year of
college in Oxford. I used to be her main tech person for computer
problems here in the midwest but she's a wee bit too far away to do
that anymore. She's enjoying the UK and the people.

Thanks again,
Charlie
charlie6067

Sub DateAddUp()
   Dim InputDate As Date
   Dim IDate As Date
   Dim FinalDate As Date
   Dim oFF As Word.FormFields
   Dim myString As String
       myString = oFF("Day").Result & "/" & oFF("Month").Result & "/"
& oFF("Year").Result
       InputDate = myString
       IDate = DateAdd("yyyy", 2, InputDate)
       oFF("FinalDate").Result = DateAdd("d", -1, IDate)
End Sub

> The same code works OK here in Word 97: could you have run it against a
> document that did not have any FormFields?
[quoted text clipped - 258 lines]
> >> >> > > > > >>>> Charlie
> >> >> > > > > >>>> charlie6067
Peter Jamieson - 13 Oct 2006 20:41 GMT
I've sent you a copy. BTW I posted another message in this thread about 29th
February which you may or may not need to think about.

Peter Jamieson
> Hi Peter,
>
[quoted text clipped - 326 lines]
>> >> >> > > > > >>>> Charlie
>> >> >> > > > > >>>> charlie6067
charlie6067 - 13 Oct 2006 22:21 GMT
Hi Peter,

Many thanks for your help and sending the file. I'll check my mail box
and also the referenced thread.

Have a great weekend!
Charlie
charlie6067

> I've sent you a copy. BTW I posted another message in this thread about 29th
> February which you may or may not need to think about.
[quoted text clipped - 330 lines]
> >> >> >> > > > > >>>> Charlie
> >> >> >> > > > > >>>> charlie6067
charlie6067 - 16 Oct 2006 21:59 GMT
Hi Peter,

I haven't yet received the file at charlie6067@yahoo.com. Would you
please resend it?
Many thanks,
Charlie

> I've sent you a copy. BTW I posted another message in this thread about 29th
> February which you may or may not need to think about.
[quoted text clipped - 330 lines]
> >> >> >> > > > > >>>> Charlie
> >> >> >> > > > > >>>> charlie6067
Peter Jamieson - 16 Oct 2006 23:12 GMT
Hi Charlie,

The first one seems to have gone to the right address. I have just re-sent.
Please check your junk mail if you don't see it.

Peter Jamieson

> Hi Peter,
>
[quoted text clipped - 362 lines]
>> >> >> >> > > > > >>>> Charlie
>> >> >> >> > > > > >>>> charlie6067
Graham Mayor - 12 Oct 2006 06:39 GMT
Did you see www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902 ? there
are examples showing how to add to field dates. You will have to adapt them
to your individual requirements.

Signature

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

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

> I've read through many posts and other articles on calculating date
> fields but can't find an answer to this specific use. I'm creating a
[quoted text clipped - 10 lines]
> Charlie
> charlie6067
charlie6067 - 12 Oct 2006 15:11 GMT
Hi Graham,

Thanks for the link! I hadn't seen that one before. It has some
solutions I can use on this and other forms.

Thanks for the help and quick reply!
Charlie
charlie6067

> Did you see www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902 ? there
> are examples showing how to add to field dates. You will have to adapt them
[quoted text clipped - 22 lines]
> > Charlie
> > charlie6067
 
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.