Age of Employee Calculation

[Douglas J. Steele]

Add a calculated field to your query, and display that field:

Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format([DOB], "mmdd"), 1, 0)

 

[Mark]

Using employee birthdate information, how does one
program the form to update and display the actual age of
the employees in all records when the form is opened?

Thank you.

 

[losmac]

I'm doing it in this way:

rivate Sub Form_Current()
If Not IsNull(Me.BDay) Then Me.Age.Value = DateDiff
("yyyy", Me.BDay, Date)
End Sub

 

[PC Datasheet]

Base your form on a query and put the following expression in an empty field in
your query grid:

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >Format(Date(),
"mmdd"), 1, 0)


**Watch word wrap!

 

[PC Datasheet]

Doug,

Look at my response. Are you a follower of John Vinson too?

Steve
PC Datasheet

Add a calculated field to your query, and display that field:

Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format([DOB], "mmdd"), 1, 0)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Using employee birthdate information, how does one
program the form to update and display the actual age of
the employees in all records when the form is opened?

Thank you.

 

[Douglas J. Steele]

I'm a respecter of John, if that counts. <g>

Actually, if I recall correctly, I was the first one to use
- IIf(Format(Date(), "mmdd") < Format([DOB], "mmdd"), 1, 0)

Most of the others were using

+ Int(Format(Date(), "mmdd") < Format([DOB]))

which relies on the fact that True is -1 and False is 0 in VBA. I started to
use the IIf construct because I didn't like relying on that fact. In VB.Net,
for instance, True is 1, not -1.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Doug,

Look at my response. Are you a follower of John Vinson too?

Steve
PC Datasheet

Add a calculated field to your query, and display that field:

Age: DateDiff("yyyy", [DOB], Date()) - IIf(Format(Date(), "mmdd") <
Format([DOB], "mmdd"), 1, 0)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Using employee birthdate information, how does one
program the form to update and display the actual age of
the employees in all records when the form is opened?

Thank you.

 

[John W. Vinson]

I'm a respecter of John, if that counts. <g>

And it's mutual, if THAT counts!

Actually, if I recall correctly, I was the first one to use
- IIf(Format(Date(), "mmdd") < Format([DOB], "mmdd"), 1, 0)

I believe that you are indeed the one from whom I learned this safer
technique.

(Just popping in from Google before going off to my nephew's
wedding... now I've got to go make him even more nervous!)

John W. Vinson

 

[Mark]

That is very helpful, and it handles the odd case where a
birthdate is not given.

Is there any way to program this code to update all the
records automatically when the form is opened?

Thanks

 

[Douglas J. Steele]

You don't want to store age in a table! Create a query based on that table,
and add a calculated field that computes their age everytime you run the
query. Use the query wherever you would otherwise have used the table.

By the way, the formula given in this post will be incorrect if the employee
hasn't had his/her birthday yet, due to how DateDiff works. And, of course,
it won't work in a query at all. You need to use something like:

Function CalculateAge(BirthDate As Variant) As Variant
If IsNull(BirthDate) Then
CalculateAge = Null
Else
CalculateAge = DateDiff("yyyy", Me.BDay, Date) - _
IIf(Format(Me.BDay, "mmdd") < Format(Date, "mmdd"), 1, 0)
End If
End If