Age of Employee Calculation

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

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)
 
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.
 
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
 
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!
 
Doug,

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

Steve
PC Datasheet


Douglas J. Steele said:
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)



Mark said:
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.
Click to expand...
Click to expand...
 
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)



PC Datasheet said:
Doug,

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

Steve
PC Datasheet


Douglas J. Steele said:
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)



Mark said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Douglas J. Steele said:
I'm a respecter of John, if that counts. <g>
Click to expand...

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)
Click to expand...

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
 
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
 
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
 
Back
Top