MS Office Forum / Word / Programming / October 2006
Request help with a custom date field...
|
|
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
|
|
|