how to update age according calendar year

[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

 

[John Spencer]

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

 

[raskew via AccessMonster.com]

Hi -

As DOB is a constant, while Age changes (a calculated field), it would make
sense to store DOB.

Age is not just a matter of subtracting one year from another, the current
date versus the birthdate must be taken into consideration to accurately
return age.

The Switch() function eliminates the hassle of matching parenthesis as we
must do with Iif() statements. Try the following:

Private Sub DOB_AfterUpdate()
Dim IntAge as Integer
Dim strHold as string

IntAge = DateDiff("yyyy", DOB, date) + (date < DateSerial(year(date),
month(DOB), day(DOB)))

strHold = switch(IntAge = 12, "UPSR", IntAge = 15, "PMR", IntAge = 17,
"SPM", True, "")

Me!Exam = strHold

End Sub


To return the current year: Year(date())

HTH - Bob

 

[datin]

thank you. i really appreciate your help.it works

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

 

[datin]

thank you. i really appreciate your help.it works