Age Calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to calculate and display someone's age in a form field based
on a date in another from field within Word?

Thanks!
 
Assume you have 2 text boxes in your form called Textbox1 and Textbox2. When
the Textbox2 is updated, the age will be calculated and shown in Textbox1.
Pls. see the codes below.

Private Sub TextBox2_AfterUpdate()
TextBox1.Value = DateDiff("yyyy", TextBox2.Value, Now()) + _
(Int(Format(Now(), "mmdd") < Int(Format(TextBox2.Value, "mmdd"))))
End Sub

Note: the formula is from Ken Snell [MVP] in a post at Access Database Design
 
Thanks for the quick response...I am still having trouble getting this to
work. If I understand you correctly, I am supposed to open a Word doc and
create two fields, Textbox1 and Textbox2. In Textbox1 I should open the
"Text Form Field Options" and then paste the following text into the
calculate field:

PrivateSubTextBox2_AfterUpdate()TextBox1.Value=DateDiff("yyyy",TextBox2.Value,Now())+_(Int(Format(Now(),"mmdd")<Int(Format(TextBox2.Value,"mmdd"))))End Sub

I should then be able to "lock" the word doc to use for forms and then enter
a date into Textbox2 which will cause an age to appear in Textbox1.

Thanks again!
 
Mr. Mayor,
Thanks for the post. This doc seems to be using a different field than I am
using. I am using a "text form field" in word. Do you know how I could
calculate the same formula in this type of field?
Thanks
 
Instead of using the ASK field to gather your birthdate, use your form field
to provide it.

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

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
I am still lost (def not a techie)...what would the formula be that I would
put into the Form Field Options Box?
Thanks again
 
In the Word document that you downloaded, is a field for calculating a
person's age. If you select that field and press Shift-F9, the field will
expand to show it's coding.

One line in the field is coded:
{ASK BirthDate "What is the Birthdate?"}
Delete that line, Then press Shift-F9 again to restore the field. Copy this
field into your form document, say just below where you've got the formfield
that solicits the birthdate.

Select your formfield and right-click on it. Choose 'properties'.
Set 'type' to 'Date'
Make the bookmark 'BirthDate'
Check 'calculate on exit.

Now protect the document for forms, type in the birthdate into your
formfield, the press the Tab key.

The 'age' calculation field should now output the date of birth.

Cheers
 
You don't put the formula in the Form Field Options box, you paste it from
the original source (not from here - use ALT+F9 to display the code) into
the document outside the form field at the place you wish the information to
be revealed. Remove the ASK field, so that the result appears as below. In
the form field that collects the date, change its bookmark name to BirthDate
and set the calculate on exit button

When you tab out of the birthdate field the inserted field displays
"If your Date of Birth was <Birthdate>, then your age is <Years>, <Months>
and <Days>
You can tailor this to your requirements by editing the latter part of the
construction which pastes the above.

{QUOTE{SET by {BirthDate \@ yyyy}}{SET bm {BirthDate \@ M}}{SET bd
{BirthDate \@ d}}{SET yy {DATE \@ yyyy}}{SET mm {DATE \@ M}}{SET dd {DATE \@
d}}{SET
md{=IF((mm=2),28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),31-(
(mm=4)+(mm=6)+(mm=9)+(mm=11)))}}{Set
Years{=yy-by-(mm<bm)-(mm=bm)*(dd<bd)}}{Set
Months{=MOD(12+mm-bm-(dd<bd),12)}}{Set Days{=MOD(md+dd-bd,md) \# 0}}"If your
Date of Birth was {Birthdate \@ "d MMMM yyyy"}, then your age is {Years}
Year{IF{Years}= 1 "" s}, {Months} Month{IF{Months}= 1 "" s} and {Days}
Day{IF{Days}= 1 "" s}."}


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

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
Ok...I this is making sense. One last question. How do I change the field
so that it displays the age as two numeric digits (ie 27).
Thanks all!
 
Locate the following part of the field

"If your Date of Birth was {Birthdate \@ "d MMMM yyyy"}, then your age is
{Years}
Year{IF{Years}= 1 "" s}, {Months} Month{IF{Months}= 1 "" s} and {Days}
Day{IF{Days}= 1 "" s}."

and change it to

"{Years}"

i.e.
{QUOTE{SET by {BirthDate \@ yyyy}}{SET bm {BirthDate \@ M}}{SET bd
{BirthDate \@ d}}{SET yy {DATE \@ yyyy}}{SET mm {DATE \@ M}}{SET dd {DATE \@
d}}{SET
md{=IF((mm=2),28+(mm=2)*((MOD(yy,4)=0)+(MOD(yy,400)=0)-(MOD(yy,100)=0)),31-(
(mm=4)+(mm=6)+(mm=9)+(mm=11)))}}{Set
Years{=yy-by-(mm<bm)-(mm=bm)*(dd<bd)}}{Set
Months{=MOD(12+mm-bm-(dd<bd),12)}}{Set Days{=MOD(md+dd-bd,md) \#
0}}"{Years}"}


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

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
When I make all the necessary changes and create another textbox referred to
as BirthDate (and mark 'calculate on exit') and enter a date into this field
(and exit) the age field updates to {Years}.
Sorry to keep buggin and thanks for your patience...
 
Did you copy the fields from the source document or did you try and recreate
by hand? It sounds as though you simply typed {years} from the keyboard
instead of using CTRL+F9 to set the field boundaries {}. If you deleted all
but {years} from the appropriate segment, you would have a working field.

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

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
No, I inserted {years} where you said, replacing the text you said to remove
(and deleting the Ask field at the top of the formula)....
 
In that case when you lock the form it should show the content. Right click
the {years] field and toggle the display to force the issue.
If that doesn't fix it, zip up the document and send it to me via the link
on my web site.

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

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
Back
Top