Calculating Age in Access

  • Thread starter Thread starter forest8
  • Start date Start date
F

forest8

Hi

In my database I have 2 dates in my form: Current Date and Date of
Emplaoyment. I would like to calculate how many years my employees have been
working for me so that I can send the appropriate congratulatory messages.

How do I do this? I looked at DateDiff but couldn't figure out how to
change it for my purposes.

Thank you in advance for your help.
 
Public Function fAge(dtmDOB, Optional dtmDate)
'Returns the Age in years, for dtmDOB.
'Age calculated as of dtmDate, or as of today if dtmDate is missing.

If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then
'set to today's date

If IsDate(dtmDOB) Then 'If date passed, then calculate age
fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _
(DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) > dtmDate)
Else
fAge = Null
End If

End Function

Or you can use one of the following expressions
'Fails if DOB is null
CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

'Returns Null if DOB is Null
CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") >
Format(Date(),"mmdd"))

DateDiff("yyyy",DOB,Date()) + Format(DOB,"mmdd") > Format(Date(),"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Hi there

I'm a bit confused about where I should put one of the expressions exactly
in my database.

I was hoping to use this expression:
'Fails if DOB is null
CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

But as I said, where do I put it?

Also, does it matter if the date format is mm/dd/yyyy?

Thank you




John Spencer said:
Public Function fAge(dtmDOB, Optional dtmDate)
'Returns the Age in years, for dtmDOB.
'Age calculated as of dtmDate, or as of today if dtmDate is missing.

If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then
'set to today's date

If IsDate(dtmDOB) Then 'If date passed, then calculate age
fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _
(DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) > dtmDate)
Else
fAge = Null
End If

End Function

Or you can use one of the following expressions
'Fails if DOB is null
CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

'Returns Null if DOB is Null
CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") >
Format(Date(),"mmdd"))

DateDiff("yyyy",DOB,Date()) + Format(DOB,"mmdd") > Format(Date(),"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi

In my database I have 2 dates in my form: Current Date and Date of
Emplaoyment. I would like to calculate how many years my employees have been
working for me so that I can send the appropriate congratulatory messages.

How do I do this? I looked at DateDiff but couldn't figure out how to
change it for my purposes.

Thank you in advance for your help.
Click to expand...
.
Click to expand...
 
You use the expression as a calculated field in a query
Field: CurrentAge: Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

As the control source of a control in a form or report
= Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

The format of a date does not matter as long as you are using a datetime type
of data. If you are using a string then you do need to convert the string
into a datetime type.

If you are trying to identify the employees that have an anniversary during a
specific time frame (for example, all employees with an employment anniversary
this month or this week or tomorrow) then that is a different question.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi there

I'm a bit confused about where I should put one of the expressions exactly
in my database.

I was hoping to use this expression:
'Fails if DOB is null
CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

But as I said, where do I put it?

Also, does it matter if the date format is mm/dd/yyyy?

Thank you




John Spencer said:
Public Function fAge(dtmDOB, Optional dtmDate)
'Returns the Age in years, for dtmDOB.
'Age calculated as of dtmDate, or as of today if dtmDate is missing.

If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then
'set to today's date

If IsDate(dtmDOB) Then 'If date passed, then calculate age
fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _
(DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) > dtmDate)
Else
fAge = Null
End If

End Function

Or you can use one of the following expressions
'Fails if DOB is null
CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

'Returns Null if DOB is Null
CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") >
Format(Date(),"mmdd"))

DateDiff("yyyy",DOB,Date()) + Format(DOB,"mmdd") > Format(Date(),"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi

In my database I have 2 dates in my form: Current Date and Date of
Emplaoyment. I would like to calculate how many years my employees have been
working for me so that I can send the appropriate congratulatory messages.

How do I do this? I looked at DateDiff but couldn't figure out how to
change it for my purposes.

Thank you in advance for your help.
Click to expand...
.
Click to expand...
Click to expand...
 
Thank you. Until now, I have been able to use little queries and programming
but have progressed to a point where I might need to investigate adding more
programming.

Thank you again.

John Spencer said:
You use the expression as a calculated field in a query
Field: CurrentAge: Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

As the control source of a control in a form or report
= Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

The format of a date does not matter as long as you are using a datetime type
of data. If you are using a string then you do need to convert the string
into a datetime type.

If you are trying to identify the employees that have an anniversary during a
specific time frame (for example, all employees with an employment anniversary
this month or this week or tomorrow) then that is a different question.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi there

I'm a bit confused about where I should put one of the expressions exactly
in my database.

I was hoping to use this expression:
'Fails if DOB is null
CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

But as I said, where do I put it?

Also, does it matter if the date format is mm/dd/yyyy?

Thank you




John Spencer said:
Public Function fAge(dtmDOB, Optional dtmDate)
'Returns the Age in years, for dtmDOB.
'Age calculated as of dtmDate, or as of today if dtmDate is missing.

If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then
'set to today's date

If IsDate(dtmDOB) Then 'If date passed, then calculate age
fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _
(DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) > dtmDate)
Else
fAge = Null
End If

End Function

Or you can use one of the following expressions
'Fails if DOB is null
CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

'Returns Null if DOB is Null
CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") >
Format(Date(),"mmdd"))

DateDiff("yyyy",DOB,Date()) + Format(DOB,"mmdd") > Format(Date(),"mmdd")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

forest8 wrote:
Hi

In my database I have 2 dates in my form: Current Date and Date of
Emplaoyment. I would like to calculate how many years my employees have been
working for me so that I can send the appropriate congratulatory messages.

How do I do this? I looked at DateDiff but couldn't figure out how to
change it for my purposes.

Thank you in advance for your help.

.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top