Request help with a custom date field...

[charlie6067]

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]

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

 

[Dave Lett]

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

 

[Greg Maxey]

Dave,

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

 

[charlie6067]

Greg,

Many thanks again for your great help and quick response.

Charlie
charlie6067

 

[charlie6067]

Hi Dave,

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

 

[Dave Lett]

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?

 

[Greg Maxey]

Dave,

Yep to both.

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?

 

[charlie6067]

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

 

[Greg Maxey]

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.


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

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,

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

 

[Graham Mayor]

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.

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[charlie6067]

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,

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.


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

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,

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

Dave Lett wrote:
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 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

 

[charlie6067]

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

 

[Greg Maxey]

Well there is no tax on thanks yet! Glad I can help.

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,

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.


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

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

charlie6067 wrote:
Hi Dave,

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

Dave Lett wrote:
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 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

 

[charlie6067]

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.

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,

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.


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


charlie6067 wrote:
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

charlie6067 wrote:
Hi Dave,

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

Dave Lett wrote:
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 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]

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,

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.

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


Greg Maxey wrote:
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.


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


charlie6067 wrote:
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

charlie6067 wrote:
Hi Dave,

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

Dave Lett wrote:
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 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

 

[charlie6067]

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,

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,

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.

charlie6067 wrote:
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


Greg Maxey wrote:
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.


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


charlie6067 wrote:
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

charlie6067 wrote:
Hi Dave,

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

Dave Lett wrote:
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 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]

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.


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

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,

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,

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


Greg Maxey wrote:
Well there is no tax on thanks yet! Glad I can help.

charlie6067 wrote:
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


Greg Maxey wrote:
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.


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


charlie6067 wrote:
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

charlie6067 wrote:
Hi Dave,

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

Dave Lett wrote:
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 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

 

[charlie6067]

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,

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.


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

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,

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

charlie6067 wrote:
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


Greg Maxey wrote:
Well there is no tax on thanks yet! Glad I can help.

charlie6067 wrote:
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


Greg Maxey wrote:
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.


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


charlie6067 wrote:
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

charlie6067 wrote:
Hi Dave,

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

Dave Lett wrote:
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 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

 

[Peter Jamieson]

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