Using Code

[Hez]

Hi all,

I must admit, all I do in Access is puddle around. But I have been
searching for how I can correctly show age in a database I am making,
and have discovered, as Allen Browne says,
"These examples do not work reliably:
Format(Date() - DOB, "yyyy")
DateDiff("y", DOB, Date)
Int(DateDiff("d", DOB, Date)/365.25)"

And there is the code there ready to use, for the accurate version. So
here's my question....where do I put the code???
I know, a little knowledge is a dangerous thing!
Thanks in anticipation!

[Ed Robichaud]

Put the following in any module of your database:

=====================================
Function Age(varBirthDate As Variant)
Dim varAge As Variant

If IsNull(varBirthDate) Then Age = 0: Exit Function

varAge = DateDiff("yyyy", varBirthDate, Now)

If Date < DateSerial(Year(Now), Month (varBirthDate),
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = varAge

End Function
====================================
Use the function in any expression in control sorces on your form/reports or
in queries. For example:
"=Age([MyDateField]"

-Ed
"Hez" <hm*****@swin.edu.au> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi all,

I must admit, all I do in Access is puddle around. But I have been
searching for how I can correctly show age in a database I am making,
and have discovered, as Allen Browne says,
"These examples do not work reliably:
Format(Date() - DOB, "yyyy")
DateDiff("y", DOB, Date)
Int(DateDiff("d", DOB, Date)/365.25)"

And there is the code there ready to use, for the accurate version. So
here's my question....where do I put the code???
I know, a little knowledge is a dangerous thing!
Thanks in anticipation!

[Hez]

Thanks Ed for your prompt reply and for the code. Not to be obtuse, are
you saying that I can copy the code and paste it into the 'exoression
builder" dialogue box of the control source area of the properties box
pertaining to the text box I want the answer to appear in??

[Ed Robichaud]

Nope. Copy the code and paste it into a MODULE. Go to the database window
and select "module", then select any existing one or create a new one, then
paste the code into it. You now have a function (a code procedure that
returns a value) resident in memory whenever that database is open. You put
it to use by "calling the function". For example, on a form, create an
unbound text box and set its control source to:
=Age(MyBirthDateField]
Substitute the field name of the date field that contains DOB/birthdate/etc.
That form control will now display calculated age.
-Ed
"Hez" <hm*****@swin.edu.au> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Thanks Ed for your prompt reply and for the code. Not to be obtuse, are
you saying that I can copy the code and paste it into the 'exoression
builder" dialogue box of the control source area of the properties box
pertaining to the text box I want the answer to appear in??

[Hez]

Thanks Ed, got it now. Greatly appreciate your help.

[Hez]

Thanks Ed, got it now. Greatly appreciate your help.