how to update age according calendar year

  • Thread starter Thread starter datin
  • Start date Start date
D

datin

hi, currently im helping HR to determine staffs dependants.So, i created
table where i calculate age. age is important to determine whether the kids
will attend public examination or not. my problem is i need to key in date of
birth every year to update age and public exam list.

Question 1 : please help me figure out this problem
my event procedure as below:

Private Sub DOB_AfterUpdate()
Dim age1 As String
Me!Age = DateDiff("yyyy", DOB, Date)
Refresh
If Me!Age = 15 Then
Me!Exam = "PMR"
Else
If Me!Age = 17 Then
Me!Exam = "SPM"
Else
If Me!Age = 12 Then
Me!Exam = "UPSR"
Else
Me!Exam = ""
End If
End If
End If
End Sub

question 2:
how to display current year ?

If Me!Age = 15 Then
Me!Exam = "PMR"
Me!Year= " ?"

thank you and regards
 
Do not store Age. Calculate it when it is needed.
Do not store Exam if it is dependent on age. Calculate it when it is needed.

Calculate age with an expression. You expression may not be totally accurate
since it would return an age of 1 for someone born on Dec 31, 2007 when the
current date was January 1, 2008. Basically your expression calculates the
difference between the years of the two dates (2008-2007 = 1).

For an accurate age in years you can use
DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") > Format(Date(),"mmdd"))

For exam, you could add a table ExamTypes with the name of the Exam and the
Age in years
EType: Age
PMR : 15
SPM : 17
UPSR : 12

Now all you have to do is add that table to a query and join Age to the
Calculated age.

Another option is to run an update query to update all the Age fields.
UPDATE [Your Table]
Set [Age] =
DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") > Format(Date(),"mmdd"))

If age needs to be calculated as of a specific date then replace Date() with
that specific date. For instance as of Dec 31, 2008

DateDiff("yyyy",DOB,#2008-12-31# + Int(Format(DOB,"mmdd") >
Format(#2008-12-31#,"mmdd"))

As for the last question:
Current Year is
Year(Date())


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
thank you. i really appreciate your help.it works

John Spencer said:
Do not store Age. Calculate it when it is needed.
Do not store Exam if it is dependent on age. Calculate it when it is needed.

Calculate age with an expression. You expression may not be totally accurate
since it would return an age of 1 for someone born on Dec 31, 2007 when the
current date was January 1, 2008. Basically your expression calculates the
difference between the years of the two dates (2008-2007 = 1).

For an accurate age in years you can use
DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") > Format(Date(),"mmdd"))

For exam, you could add a table ExamTypes with the name of the Exam and the
Age in years
EType: Age
PMR : 15
SPM : 17
UPSR : 12

Now all you have to do is add that table to a query and join Age to the
Calculated age.

Another option is to run an update query to update all the Age fields.
UPDATE [Your Table]
Set [Age] =
DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") > Format(Date(),"mmdd"))

If age needs to be calculated as of a specific date then replace Date() with
that specific date. For instance as of Dec 31, 2008

DateDiff("yyyy",DOB,#2008-12-31# + Int(Format(DOB,"mmdd") >
Format(#2008-12-31#,"mmdd"))

As for the last question:
Current Year is
Year(Date())


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
hi, currently im helping HR to determine staffs dependants.So, i created
table where i calculate age. age is important to determine whether the kids
will attend public examination or not. my problem is i need to key in date of
birth every year to update age and public exam list.

Question 1 : please help me figure out this problem
my event procedure as below:

Private Sub DOB_AfterUpdate()
Dim age1 As String
Me!Age = DateDiff("yyyy", DOB, Date)
Refresh
If Me!Age = 15 Then
Me!Exam = "PMR"
Else
If Me!Age = 17 Then
Me!Exam = "SPM"
Else
If Me!Age = 12 Then
Me!Exam = "UPSR"
Else
Me!Exam = ""
End If
End If
End If
End Sub

question 2:
how to display current year ?

If Me!Age = 15 Then
Me!Exam = "PMR"
Me!Year= " ?"

thank you and regards
Click to expand...
Click to expand...
 
Back
Top