Age from Date Function

[lcox400w]

I am using Allen Browne's date function I got from another posting. I am
trying to get it to work. I need to comepare the persons DOB from the date
an incident occurred "DateOccd". I get a #Name? when I look at the age field
using the below formula. I know the problme is in the control source as if I
use a fixed date it works fine, just doesnt work when I put inthe "DateOccd"
field. Not sure hwo to list that.

In the control source I have put:

=Age([DOB],[Me.Parent!DateOccd])

The Allen Browne's funciton is:

Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
'To use this function, put a text box on your form and sets its
controlsource
'to =Age([BirthDate], #09/21/08#)

Dim dtDOB As Date
Dim dtAsOf As Date
Dim dtBDay As Date 'Birthday in the year of calculation.

Age = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtDOB = varDOB

If Not IsDate(varAsOf) Then 'Date to calculate age from.
dtAsOf = Date
Else
dtAsOf = varAsOf
End If

If dtAsOf >= dtDOB Then 'Calculate only if it's after person
was born.
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End If
End If
End Function

 

[Allen Browne]

So this box and DOB are in the subform, but DateOccd is in the parent form?

Try:
=Age([DOB], [Form].[Parent]![DateOccd])

Alternatively, if the main form is named "frm1", you could use:
=Age([DOB], [Forms].[frm1]![DateOccd])

For anyone else following this thread, the Age() function is explained here:
http://allenbrowne.com/func-08.html

 

[lcox400w]

That worked, except I forgot one thing. I need to still be able to enter an
age of a person if I dont know their date of birth. So instead of placing
the code int he control source, I want to run it in the Form_Current event
and have the function only give me an age if the DOB is null.

But when I paste the code into the forms current event, as

Private Sub Form_Current()

Age([DOB],[Forms]![frmcaseupdate]![DateOccd])

End Sub

I get an error "Expected: =".

what am I missing?

So this box and DOB are in the subform, but DateOccd is in the parent form?

Try:
=Age([DOB], [Form].[Parent]![DateOccd])

Alternatively, if the main form is named "frm1", you could use:
=Age([DOB], [Forms].[frm1]![DateOccd])

For anyone else following this thread, the Age() function is explained here:
http://allenbrowne.com/func-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using Allen Browne's date function I got from another posting. I am
trying to get it to work. I need to comepare the persons DOB from the
date
an incident occurred "DateOccd". I get a #Name? when I look at the age
field
using the below formula. I know the problme is in the control source as
if I
use a fixed date it works fine, just doesnt work when I put inthe
"DateOccd"
field. Not sure hwo to list that.

In the control source I have put:

=Age([DOB],[Me.Parent!DateOccd])

 

[Allen Browne]

You don't want to store the age. Guess the year of birth instead.

Otherwise you have a field that is constantly going out of date. With only
200 people, changes are it's out of date every day.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That worked, except I forgot one thing. I need to still be able to enter
an
age of a person if I dont know their date of birth. So instead of placing
the code int he control source, I want to run it in the Form_Current event
and have the function only give me an age if the DOB is null.

But when I paste the code into the forms current event, as

Private Sub Form_Current()

Age([DOB],[Forms]![frmcaseupdate]![DateOccd])

End Sub

I get an error "Expected: =".

what am I missing?

So this box and DOB are in the subform, but DateOccd is in the parent
form?

Try:
=Age([DOB], [Form].[Parent]![DateOccd])

Alternatively, if the main form is named "frm1", you could use:
=Age([DOB], [Forms].[frm1]![DateOccd])

For anyone else following this thread, the Age() function is explained
here:
http://allenbrowne.com/func-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using Allen Browne's date function I got from another posting. I
am
trying to get it to work. I need to comepare the persons DOB from the
date
an incident occurred "DateOccd". I get a #Name? when I look at the age
field
using the below formula. I know the problme is in the control source
as
if I
use a fixed date it works fine, just doesnt work when I put inthe
"DateOccd"
field. Not sure hwo to list that.

In the control source I have put:

=Age([DOB],[Me.Parent!DateOccd])

 

[lcox400w]

The database is for people who commit crimes and are unknown persons. In
that case we need to store age at the time the crime was committed if their
date of birth was unknown and we dont want age to change. I need age to be
static.

The code you helped me with below would provide me the age of the person at
the time they were the victim of the crime as I'm taking the date the
incident occurred minus their birthdate so I always know how old they were
when the were victimized. But when I dont have a DOB, I have to supply an
approximate age from a witness and need that to stay static during the course
of the investigaiton.

Is their an option to get the code to work in that fashion where i can just
call the function is date of birth is null, otherwise the end user could
enter an approximate age? I want to call the function on the current event
of the form, but dont know how to get it to work properly. The function
itself works great when I make it party of the control source, but since I
need the control source to be a field in the table, I need to call the
function from the forms current event.

hope this makes sense.

You don't want to store the age. Guess the year of birth instead.

Otherwise you have a field that is constantly going out of date. With only
200 people, changes are it's out of date every day.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That worked, except I forgot one thing. I need to still be able to enter
an
age of a person if I dont know their date of birth. So instead of placing
the code int he control source, I want to run it in the Form_Current event
and have the function only give me an age if the DOB is null.

But when I paste the code into the forms current event, as

Private Sub Form_Current()

Age([DOB],[Forms]![frmcaseupdate]![DateOccd])

End Sub

I get an error "Expected: =".

what am I missing?

So this box and DOB are in the subform, but DateOccd is in the parent
form?

Try:
=Age([DOB], [Form].[Parent]![DateOccd])

Alternatively, if the main form is named "frm1", you could use:
=Age([DOB], [Forms].[frm1]![DateOccd])

For anyone else following this thread, the Age() function is explained
here:
http://allenbrowne.com/func-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using Allen Browne's date function I got from another posting. I
am
trying to get it to work. I need to comepare the persons DOB from the
date
an incident occurred "DateOccd". I get a #Name? when I look at the age
field
using the below formula. I know the problme is in the control source
as
if I
use a fixed date it works fine, just doesnt work when I put inthe
"DateOccd"
field. Not sure hwo to list that.

In the control source I have put:

=Age([DOB],[Me.Parent!DateOccd])

 

[Allen Browne]

Okay, so you are going to work with 2 fields:
- DateOfBirth
- AgeAtIncident
You can place a calculated text box next to the DateOfBirth box (it will be
bound to the expression using the Age() function), and the AgeAtIncident box
beside that.

Using both fields does open the door for bad data, where both are entered
but don't match. You probably want to block that (e.g. with a Validation
Rule that prevents people entering both.)