calculate age function utilization

[george]

Hi,

I've copied the following code from the microsoft access
mvp organization site.

'--- CODE START ---
Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef
vYears As Integer,
ByRef vMonths As Integer, ByRef vDays As Integer)
' Comments : calculates the age in Years, Months and
Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
vMonths = DateDiff("m", vDate1, vdate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1),
vdate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths,
vDate1), vdate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less
than one year
End Sub
'--- CODE END ---

Can someone help me on how to use it? I have a clients
form with a textbox, ClientBirthdate, on it and another
textbox, ClientAge. What do I need to do to display the
client's age in the corresponding textbox?

thanks in advance, george

 

[Jeff Conrad]

Can I offer a different suggestion?

1. Forget the code provided.

2. On the form put this as the Control Source for ClientAge:

=DateDiff("yyyy",[ClientBirthdate],Date())-IIf(Format([ClientBirthdate],"mmdd")>Format(Date(),"mmdd"
),1,0)

As you make changes to the ClientBirthdate field the ClientAge text box will display the current
age.

3. I would lock the ClientAge control and set the Enabled property to False so it cannot be altered.

4. Remember that this ClientAge information should not be saved in any table because it is a
calculated field and you are guaranteed to have all the records having inaccurate information in one
year. Just display the age on forms and reports when needed.

 

[george]

Jeff, thanks a lot for your answer

However I do need to handle months and days in a person's
age. I am requested to use "insurance age". This means
that a person who's past 6 months from his last birthday
counts as one year older, ie 30 years, 7 months, 20 days
counts as 31 years.

In the past my question was posted by someone else (on May
30th 2004 I think) but I cannot retrieve the answer any
more.

george

-----Original Message-----
Can I offer a different suggestion?

1. Forget the code provided.

2. On the form put this as the Control Source for ClientAge:

=DateDiff("yyyy",[ClientBirthdate],Date())-IIf(Format

([ClientBirthdate],"mmdd")>Format(Date(),"mmdd"
),1,0)

As you make changes to the ClientBirthdate field the

ClientAge text box will display the current

age.

3. I would lock the ClientAge control and set the Enabled

property to False so it cannot be altered.

4. Remember that this ClientAge information should not be

saved in any table because it is a

calculated field and you are guaranteed to have all the

records having inaccurate information in one

 

[Jeff Conrad]

Ok, let's try this.

1. Go to Doug Steele's site for his Date2Diff function:

http://members.rogers.com/douglas.j.steele/Diff2Dates.html

2. Copy and paste all that code to a new standard module. Compile the code and save the module as
modDateDifference.

3. Remove any Control Source for the ClientAge text box control.

4. Lock the ClientAge text box and set the Enabled property to False.

5. In my example the ClientAge text box is called txtClientAge.

6. In the AfterUpdate event for the ClientBirthdate field enter this code in the VBE window:

Private Sub ClientBirthdate_AfterUpdate()
If IsNull(Me.ClientBirthdate) Then
' No date filled in
Me.txtClientAge = "No Birthdate Entered"
Else
' Date was filled in so calculate current age
Me.txtClientAge = Diff2Dates("ymd", Me.ClientBirthdate, Date, True)
End If
End Sub

7. Add the same code to the Form's Current event:

Private Sub Form_Current()
If IsNull(Me.ClientBirthdate) Then
' No date filled in
Me.txtClientAge = "No Birthdate Entered"
Else
' Date was filled in so calculate current age
Me.txtClientAge = Diff2Dates("ymd", Me.ClientBirthdate, Date, True)
End If
End Sub

8. Compile the code and save the form changes.

9. Now test entering a new record. After entering a birthdate the client age text box will display
the age in years, months, and days.

Will this suffice?
Please note that the output will be a text string. If you actually need to have some automatic
evaluation of the age and subsequently DO something else as a result, then this technique may not
suffice.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Jeff, thanks a lot for your answer

However I do need to handle months and days in a person's
age. I am requested to use "insurance age". This means
that a person who's past 6 months from his last birthday
counts as one year older, ie 30 years, 7 months, 20 days
counts as 31 years.

In the past my question was posted by someone else (on May
30th 2004 I think) but I cannot retrieve the answer any
more.

george

-----Original Message-----
Can I offer a different suggestion?

1. Forget the code provided.

2. On the form put this as the Control Source for ClientAge:

=DateDiff("yyyy",[ClientBirthdate],Date())-IIf(Format

([ClientBirthdate],"mmdd")>Format(Date(),"mmdd"
),1,0)

As you make changes to the ClientBirthdate field the

ClientAge text box will display the current

age.

3. I would lock the ClientAge control and set the Enabled

property to False so it cannot be altered.

4. Remember that this ClientAge information should not be

saved in any table because it is a

calculated field and you are guaranteed to have all the

records having inaccurate information in one

year. Just display the age on forms and reports when needed.

 

[george]

Jeff thanks a lot for your time and efford.
Your code was very helpful to me
george

-----Original Message-----
Ok, let's try this.

1. Go to Doug Steele's site for his Date2Diff function:

http://members.rogers.com/douglas.j.steele/Diff2Dates.html

2. Copy and paste all that code to a new standard module.

Compile the code and save the module as

modDateDifference.

3. Remove any Control Source for the ClientAge text box control.

4. Lock the ClientAge text box and set the Enabled property to False.

5. In my example the ClientAge text box is called txtClientAge.

6. In the AfterUpdate event for the ClientBirthdate field

enter this code in the VBE window:

Private Sub ClientBirthdate_AfterUpdate()
If IsNull(Me.ClientBirthdate) Then
' No date filled in
Me.txtClientAge = "No Birthdate Entered"
Else
' Date was filled in so calculate current age
Me.txtClientAge = Diff2Dates("ymd",

Me.ClientBirthdate, Date, True)

End If
End Sub

7. Add the same code to the Form's Current event:

Private Sub Form_Current()
If IsNull(Me.ClientBirthdate) Then
' No date filled in
Me.txtClientAge = "No Birthdate Entered"
Else
' Date was filled in so calculate current age
Me.txtClientAge = Diff2Dates("ymd",

Me.ClientBirthdate, Date, True)

End If
End Sub

8. Compile the code and save the form changes.

9. Now test entering a new record. After entering a

birthdate the client age text box will display

the age in years, months, and days.

Will this suffice?
Please note that the output will be a text string. If you

actually need to have some automatic

evaluation of the age and subsequently DO something else

as a result, then this technique may not

suffice.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Jeff, thanks a lot for your answer

However I do need to handle months and days in a person's
age. I am requested to use "insurance age". This means
that a person who's past 6 months from his last birthday
counts as one year older, ie 30 years, 7 months, 20 days
counts as 31 years.

In the past my question was posted by someone else (on May
30th 2004 I think) but I cannot retrieve the answer any
more.

george

-----Original Message-----
Can I offer a different suggestion?

1. Forget the code provided.

2. On the form put this as the Control Source for ClientAge:

=DateDiff("yyyy",[ClientBirthdate],Date())-IIf(Format

([ClientBirthdate],"mmdd")>Format(Date(),"mmdd"
),1,0)

As you make changes to the ClientBirthdate field the

ClientAge text box will display the current

age.

3. I would lock the ClientAge control and set the

Enabled
property to False so it cannot be altered.

4. Remember that this ClientAge information should not

be
saved in any table because it is a

calculated field and you are guaranteed to have all the

records having inaccurate information in one

year. Just display the age on forms and reports when needed.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Hi,

I've copied the following code from the microsoft access
mvp organization site.

'--- CODE START ---
Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef
vYears As Integer,
ByRef vMonths As Integer, ByRef vDays As Integer)
' Comments : calculates the age in Years, Months and
Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
vMonths = DateDiff("m", vDate1, vdate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1),
vdate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths,
vDate1), vdate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less
than one year
End Sub
'--- CODE END ---

Can someone help me on how to use it? I have a clients
form with a textbox, ClientBirthdate, on it and another
textbox, ClientAge. What do I need to do to display the
client's age in the corresponding textbox?

thanks in advance, george

.

 

[Jeff Conrad]

You're welcome, glad to help.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Jeff thanks a lot for your time and efford.
Your code was very helpful to me
george

-----Original Message-----
Ok, let's try this.

1. Go to Doug Steele's site for his Date2Diff function:

http://members.rogers.com/douglas.j.steele/Diff2Dates.html

2. Copy and paste all that code to a new standard module.

Compile the code and save the module as

modDateDifference.

3. Remove any Control Source for the ClientAge text box control.

4. Lock the ClientAge text box and set the Enabled property to False.

5. In my example the ClientAge text box is called txtClientAge.

6. In the AfterUpdate event for the ClientBirthdate field

enter this code in the VBE window:

Private Sub ClientBirthdate_AfterUpdate()
If IsNull(Me.ClientBirthdate) Then
' No date filled in
Me.txtClientAge = "No Birthdate Entered"
Else
' Date was filled in so calculate current age
Me.txtClientAge = Diff2Dates("ymd",

Me.ClientBirthdate, Date, True)

End If
End Sub

7. Add the same code to the Form's Current event:

Private Sub Form_Current()
If IsNull(Me.ClientBirthdate) Then
' No date filled in
Me.txtClientAge = "No Birthdate Entered"
Else
' Date was filled in so calculate current age
Me.txtClientAge = Diff2Dates("ymd",

Me.ClientBirthdate, Date, True)

End If
End Sub

8. Compile the code and save the form changes.

9. Now test entering a new record. After entering a

birthdate the client age text box will display

the age in years, months, and days.

Will this suffice?
Please note that the output will be a text string. If you

actually need to have some automatic

evaluation of the age and subsequently DO something else

as a result, then this technique may not

suffice.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Jeff, thanks a lot for your answer

However I do need to handle months and days in a person's
age. I am requested to use "insurance age". This means
that a person who's past 6 months from his last birthday
counts as one year older, ie 30 years, 7 months, 20 days
counts as 31 years.

In the past my question was posted by someone else (on May
30th 2004 I think) but I cannot retrieve the answer any
more.

george

-----Original Message-----
Can I offer a different suggestion?

1. Forget the code provided.

2. On the form put this as the Control Source for
ClientAge:

=DateDiff("yyyy",[ClientBirthdate],Date())-IIf(Format
([ClientBirthdate],"mmdd")>Format(Date(),"mmdd"
),1,0)

As you make changes to the ClientBirthdate field the
ClientAge text box will display the current
age.

3. I would lock the ClientAge control and set the Enabled
property to False so it cannot be altered.

4. Remember that this ClientAge information should not be
saved in any table because it is a
calculated field and you are guaranteed to have all the
records having inaccurate information in one
year. Just display the age on forms and reports when
needed.

--
Jeff Conrad
Access Junkie
Bend, Oregon

message
Hi,

I've copied the following code from the microsoft access
mvp organization site.

'--- CODE START ---
Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef
vYears As Integer,
ByRef vMonths As Integer, ByRef vDays As Integer)
' Comments : calculates the age in Years, Months
and
Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
vMonths = DateDiff("m", vDate1, vdate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1),
vdate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths,
vDate1), vdate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less
than one year
End Sub
'--- CODE END ---

Can someone help me on how to use it? I have a clients
form with a textbox, ClientBirthdate, on it and another
textbox, ClientAge. What do I need to do to display the
client's age in the corresponding textbox?

thanks in advance, george