Calculate Age In Subform

  • Thread starter Thread starter jgarzoli
  • Start date Start date
J

jgarzoli

Hi guys/girls

I have a form with a subform. The main form is based on tblContact
and the subform is based on tblMeeting. Linked by Contact ID. On
Contact can have many meetings associated with it

In the main form I have a field that calculates age based o
birthdate. Works great. I would also like to be able to create a
age field in the subform, based on a meeting date field. So the ag
calculated, would be the age of the person at that meeting date. Th
birthdate field is located in the tblContact. And the meeting dat
field is located in tblMeeting.

I am having trouble getting a formula to calculate the age at the dat
of the meeting in the subform

Any suggestions

Thanks
 
In the main form I have a field that calculates age based on
birthdate. Works great.
Click to expand...
It would have been helpful if you had shown us that calculation...

When in doubt, always use the "full" address of the control your
addressing.
Use the DateDiff function...
DateDiff("m",[MeetingDate],Forms!frmYourMainForm![BirthDate])/12
should do it. Adjust the interval and the DateDiff formula to suit.
hth
Al Camp
 
Although your suggestion of using fully qualified control names is correct,
I'm afraid that age formula will return incorrect values.

dob=dateserial(2000,2,2)
date1=dateserial(2005,2,1)
?DateDiff("m",date1,dob)/12
-5
....also...
dob=dateserial(1999,12,31)
date1=dateserial(2000,1,1)
?DateDiff("m",date1,dob)/12
-8.33333333333333E-02

You might want to use this instead:

Public Function Age(DOB As Date, Date2 As Date) As Integer
Age = Abs(DateDiff("yyyy", DOB, Date2) - _
IIf(Format(DOB, "mmdd") <= Format(Date2, "mmdd"), 0, 1))
End Function

Then you can call it from the main form using the following syntax:
Me!frsMySubform.Form!txtAge = Age(Me!txtDOB,
Me!frsMySubform.Form!txtMeetingDate)

You can call the procedure from the subform, in either of the following
ways:
Me!txtAge = Age(Me.Parent!txtDOB, Me!txtMeetingDate)
....or...
Me!txtAge = Age(Me.Parent.Form!txtDOB, Me!txtMeetingDate)
....or...
Me!txtAge = Age(Forms!frmMainForm!txtDOB, Me!txtMeetingDate)

You can even put it in the control's DefaultValue property, and simply
Requery the control:
Me!txtAge.Requery

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Al Camp said:
In the main form I have a field that calculates age based on
birthdate. Works great.
Click to expand...
It would have been helpful if you had shown us that calculation...

When in doubt, always use the "full" address of the control your
addressing.
Use the DateDiff function...
DateDiff("m",[MeetingDate],Forms!frmYourMainForm![BirthDate])/12
should do it. Adjust the interval and the DateDiff formula to suit.
hth
Al Camp

jgarzoli said:
Hi guys/girls,

I have a form with a subform. The main form is based on tblContact,
and the subform is based on tblMeeting. Linked by Contact ID. One
Contact can have many meetings associated with it.

In the main form I have a field that calculates age based on
birthdate. Works great. I would also like to be able to create an
age field in the subform, based on a meeting date field. So the age
calculated, would be the age of the person at that meeting date. The
birthdate field is located in the tblContact. And the meeting date
field is located in tblMeeting.

I am having trouble getting a formula to calculate the age at the date
of the meeting in the subform.

Any suggestions?

Thanks!
Click to expand...
Click to expand...
 
There's also a slightly simpler version:
Abs(DateDiff("yyyy", DOB, Date2) + (Format(DOB, "mmdd") > Format(Date2,
"mmdd")))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham R Seach said:
Although your suggestion of using fully qualified control names is
correct, I'm afraid that age formula will return incorrect values.

dob=dateserial(2000,2,2)
date1=dateserial(2005,2,1)
?DateDiff("m",date1,dob)/12
-5
...also...
dob=dateserial(1999,12,31)
date1=dateserial(2000,1,1)
?DateDiff("m",date1,dob)/12
-8.33333333333333E-02

You might want to use this instead:

Public Function Age(DOB As Date, Date2 As Date) As Integer
Age = Abs(DateDiff("yyyy", DOB, Date2) - _
IIf(Format(DOB, "mmdd") <= Format(Date2, "mmdd"), 0, 1))
End Function

Then you can call it from the main form using the following syntax:
Me!frsMySubform.Form!txtAge = Age(Me!txtDOB,
Me!frsMySubform.Form!txtMeetingDate)

You can call the procedure from the subform, in either of the following
ways:
Me!txtAge = Age(Me.Parent!txtDOB, Me!txtMeetingDate)
...or...
Me!txtAge = Age(Me.Parent.Form!txtDOB, Me!txtMeetingDate)
...or...
Me!txtAge = Age(Forms!frmMainForm!txtDOB, Me!txtMeetingDate)

You can even put it in the control's DefaultValue property, and simply
Requery the control:
Me!txtAge.Requery

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Al Camp said:
In the main form I have a field that calculates age based on
birthdate. Works great.
Click to expand...
It would have been helpful if you had shown us that calculation...

When in doubt, always use the "full" address of the control your
addressing.
Use the DateDiff function...
DateDiff("m",[MeetingDate],Forms!frmYourMainForm![BirthDate])/12
should do it. Adjust the interval and the DateDiff formula to suit.
hth
Al Camp

jgarzoli said:
Hi guys/girls,

I have a form with a subform. The main form is based on tblContact,
and the subform is based on tblMeeting. Linked by Contact ID. One
Contact can have many meetings associated with it.

In the main form I have a field that calculates age based on
birthdate. Works great. I would also like to be able to create an
age field in the subform, based on a meeting date field. So the age
calculated, would be the age of the person at that meeting date. The
birthdate field is located in the tblContact. And the meeting date
field is located in tblMeeting.

I am having trouble getting a formula to calculate the age at the date
of the meeting in the subform.

Any suggestions?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Thanks Graham,
I really just wanted to emphasize the "addressing" of the date values in
the calculation, and the use of the DateDiff function.
I realized that my Age calculation was not perfect, and only meant as a
conceptual example...so I added the comment...
"Adjust the interval and the DateDiff formula to suit."

Thanks for the info...
Al Camp

Graham R Seach said:
Although your suggestion of using fully qualified control names is
correct, I'm afraid that age formula will return incorrect values.

dob=dateserial(2000,2,2)
date1=dateserial(2005,2,1)
?DateDiff("m",date1,dob)/12
-5
...also...
dob=dateserial(1999,12,31)
date1=dateserial(2000,1,1)
?DateDiff("m",date1,dob)/12
-8.33333333333333E-02

You might want to use this instead:

Public Function Age(DOB As Date, Date2 As Date) As Integer
Age = Abs(DateDiff("yyyy", DOB, Date2) - _
IIf(Format(DOB, "mmdd") <= Format(Date2, "mmdd"), 0, 1))
End Function

Then you can call it from the main form using the following syntax:
Me!frsMySubform.Form!txtAge = Age(Me!txtDOB,
Me!frsMySubform.Form!txtMeetingDate)

You can call the procedure from the subform, in either of the following
ways:
Me!txtAge = Age(Me.Parent!txtDOB, Me!txtMeetingDate)
...or...
Me!txtAge = Age(Me.Parent.Form!txtDOB, Me!txtMeetingDate)
...or...
Me!txtAge = Age(Forms!frmMainForm!txtDOB, Me!txtMeetingDate)

You can even put it in the control's DefaultValue property, and simply
Requery the control:
Me!txtAge.Requery

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Al Camp said:
In the main form I have a field that calculates age based on
birthdate. Works great.
Click to expand...
It would have been helpful if you had shown us that calculation...

When in doubt, always use the "full" address of the control your
addressing.
Use the DateDiff function...
DateDiff("m",[MeetingDate],Forms!frmYourMainForm![BirthDate])/12
should do it. Adjust the interval and the DateDiff formula to suit.
hth
Al Camp

jgarzoli said:
Hi guys/girls,

I have a form with a subform. The main form is based on tblContact,
and the subform is based on tblMeeting. Linked by Contact ID. One
Contact can have many meetings associated with it.

In the main form I have a field that calculates age based on
birthdate. Works great. I would also like to be able to create an
age field in the subform, based on a meeting date field. So the age
calculated, would be the age of the person at that meeting date. The
birthdate field is located in the tblContact. And the meeting date
field is located in tblMeeting.

I am having trouble getting a formula to calculate the age at the date
of the meeting in the subform.

Any suggestions?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
This is neat... I'll give it a try.
Thanks,
Al Camp

Graham R Seach said:
There's also a slightly simpler version:
Abs(DateDiff("yyyy", DOB, Date2) + (Format(DOB, "mmdd") > Format(Date2,
"mmdd")))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Graham R Seach said:
Although your suggestion of using fully qualified control names is
correct, I'm afraid that age formula will return incorrect values.

dob=dateserial(2000,2,2)
date1=dateserial(2005,2,1)
?DateDiff("m",date1,dob)/12
-5
...also...
dob=dateserial(1999,12,31)
date1=dateserial(2000,1,1)
?DateDiff("m",date1,dob)/12
-8.33333333333333E-02

You might want to use this instead:

Public Function Age(DOB As Date, Date2 As Date) As Integer
Age = Abs(DateDiff("yyyy", DOB, Date2) - _
IIf(Format(DOB, "mmdd") <= Format(Date2, "mmdd"), 0, 1))
End Function

Then you can call it from the main form using the following syntax:
Me!frsMySubform.Form!txtAge = Age(Me!txtDOB,
Me!frsMySubform.Form!txtMeetingDate)

You can call the procedure from the subform, in either of the following
ways:
Me!txtAge = Age(Me.Parent!txtDOB, Me!txtMeetingDate)
...or...
Me!txtAge = Age(Me.Parent.Form!txtDOB, Me!txtMeetingDate)
...or...
Me!txtAge = Age(Forms!frmMainForm!txtDOB, Me!txtMeetingDate)

You can even put it in the control's DefaultValue property, and simply
Requery the control:
Me!txtAge.Requery

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Al Camp said:
In the main form I have a field that calculates age based on
birthdate. Works great.
It would have been helpful if you had shown us that calculation...

When in doubt, always use the "full" address of the control your
addressing.
Use the DateDiff function...
DateDiff("m",[MeetingDate],Forms!frmYourMainForm![BirthDate])/12
should do it. Adjust the interval and the DateDiff formula to suit.
hth
Al Camp

Hi guys/girls,

I have a form with a subform. The main form is based on tblContact,
and the subform is based on tblMeeting. Linked by Contact ID. One
Contact can have many meetings associated with it.

In the main form I have a field that calculates age based on
birthdate. Works great. I would also like to be able to create an
age field in the subform, based on a meeting date field. So the age
calculated, would be the age of the person at that meeting date. The
birthdate field is located in the tblContact. And the meeting date
field is located in tblMeeting.

I am having trouble getting a formula to calculate the age at the date
of the meeting in the subform.

Any suggestions?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top