A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating Age in Access



 
 
Thread Tools Display Modes
  #1  
Old February 25th, 2010, 07:26 AM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Calculating Age in Access

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.

  #2  
Old February 25th, 2010, 09:29 AM posted to microsoft.public.access.gettingstarted
biganthony via AccessMonster.com
external usenet poster
 
Posts: 49
Default Calculating Age in Access

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.



Hello,

I have used this function from Graham Seach:

Public Function Diff2Dates(Interval As String, Date1 As Variant, Date2 As
Variant, _
Optional ShowZero As Boolean = False) As Variant
'Author: ? Copyright 2001 Pacific Database Pty Limited
' Graham R Seach MCP MVP
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
' This code is freeware. Enjoy...
' (*) Amendments suggested by Douglas J. Steele MVP
'
'Description: This function calculates the number of years,
' months, days, hours, minutes and seconds between
' two dates, as elapsed time.
'
'Inputs: Interval: Intervals to be displayed (a string)
' Date1: The lower date (see below)
' Date2: The higher date (see below)
' ShowZero: Boolean to select showing zero elements
'
'Outputs: On error: Null
' On no error: Variant containing the number of years,
' months, days, hours, minutes & seconds between
' the two dates, depending on the display interval
' selected.
' If Date1 is greater than Date2, the result will
' be a negative value.
' The function compensates for the lack of any intervals
' not listed. For example, if Interval lists "m", but
' not "y", the function adds the value of the year
' component to the month component.
' If ShowZero is True, and an output element is zero, it
' is displayed. However, if ShowZero is False or
' omitted, no zero-value elements are displayed.
' For example, with ShowZero = False, Interval = "ym",
' elements = 0 & 1 respectively, the output string
' will be "1 month" - not "0 years 1 month".

On Error GoTo Err_Diff2Dates

Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffYears As Long
Dim lngDiffMonths As Long
Dim lngDiffDays As Long
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim lngDiffSeconds As Long
Dim varTemp As Variant
Const INTERVALS As String = "dmyhns"

'Check that Interval contains only valid characters
Interval = LCase$(Interval)
For intCounter = 1 To Len(Interval)
If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
Exit Function
End If
Next intCounter

'Check that valid dates have been entered
If IsNull(Date1) Then Exit Function
If IsNull(Date2) Then Exit Function
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function

'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If
Diff2Dates = Null
varTemp = Null

'What intervals are supplied
booCalcYears = (InStr(1, Interval, "y") 0)
booCalcMonths = (InStr(1, Interval, "m") 0)
booCalcDays = (InStr(1, Interval, "d") 0)
booCalcHours = (InStr(1, Interval, "h") 0)
booCalcMinutes = (InStr(1, Interval, "n") 0)
booCalcSeconds = (InStr(1, Interval, "s") 0)

'Get the cumulative differences
If booCalcYears Then
lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
IIf(Format$(Date1, "mmddhhnnss") = Format$(Date2, "mmddhhnnss")
, 0, 1)
Date1 = DateAdd("yyyy", lngDiffYears, Date1)
End If
If booCalcMonths Then
lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
IIf(Format$(Date1, "ddhhnnss") = Format$(Date2, "ddhhnnss"), 0,
1)
Date1 = DateAdd("m", lngDiffMonths, Date1)
End If
If booCalcDays Then
lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
IIf(Format$(Date1, "hhnnss") = Format$(Date2, "hhnnss"), 0, 1)
Date1 = DateAdd("d", lngDiffDays, Date1)
End If
If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
IIf(Format$(Date1, "nnss") = Format$(Date2, "nnss"), 0, 1)
Date1 = DateAdd("h", lngDiffHours, Date1)
End If
If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
IIf(Format$(Date1, "ss") = Format$(Date2, "ss"), 0, 1)
Date1 = DateAdd("n", lngDiffMinutes, Date1)
End If
If booCalcSeconds Then
lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
Date1 = DateAdd("s", lngDiffSeconds, Date1)
End If
If booCalcYears And (lngDiffYears 0 Or ShowZero) Then
varTemp = lngDiffYears & IIf(lngDiffYears 1, " years", " year")
End If
If booCalcMonths And (lngDiffMonths 0 Or ShowZero) Then
If booCalcMonths Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMonths & IIf(lngDiffMonths 1, " months", "
month")
End If
End If
If booCalcDays And (lngDiffDays 0 Or ShowZero) Then
If booCalcDays Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffDays & IIf(lngDiffDays 1, " days", " day")
End If
End If
If booCalcHours And (lngDiffHours 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours 1, " hours", " hour")
End If
End If
If booCalcMinutes And (lngDiffMinutes 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMinutes & IIf(lngDiffMinutes 1, " minutes", "
minute")
End If
End If
If booCalcSeconds And (lngDiffSeconds 0 Or ShowZero) Then
If booCalcSeconds Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffSeconds & IIf(lngDiffSeconds 1, " seconds", "
second")
End If
End If
If booSwapped Then
varTemp = "-" & varTemp
End If
Diff2Dates = Trim$(varTemp)

End_Diff2Dates:
Exit Function

Err_Diff2Dates:
Resume End_Diff2Dates

End Function


An example of how I used it on a form is as follows:

Me.LengthofServiceYears = Diff2Dates("ymd", Me.BeginEmployment, Me.
EndEmployment, True)

Regards,
Anthony

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201002/1

  #3  
Old February 25th, 2010, 02:15 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Calculating Age in Access

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.

  #4  
Old February 25th, 2010, 08:02 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Calculating Age in Access

Do you want to identify when an employee's 'anniversary' is coming up so that
a message can be sent? If so you could use a function like this:

Public Function Anniversary(dtmHired As Date, intWeekStarting As Integer)

Dim dtmWeekStart As Date, dtmWeekEnd As Date, dtmAnniversaryDate As Date
Dim n As Integer

dtmWeekStart = VBA.Date - Weekday(VBA.Date, intWeekStarting) + 1
dtmWeekEnd = DateAdd("d", 6, dtmWeekStart)

For n = 1 To 50
dtmAnniversaryDate = DateAdd("yyyy", n, dtmHired)
If dtmAnniversaryDate = dtmWeekStart Then
If dtmAnniversaryDate = dtmWeekEnd Then
Anniversary = "Anniversary " & n & " this week on " &
dtmAnniversaryDate
Else
Anniversary = "Next anniversary (" & n & ") on " &
dtmAnniversaryDate
End If
Exit For
End If
Next n

End Function

This will identify if an employee's 'anniversary' is in the current week and
return a string like:

'Anniversary 6 this week on 02/23/2010'

or if not in the current week a string like:

'Next anniversary (6) on 03/23/2010'

You can call it in a query, passing the employee's hire date and the first
day of the week from which the current week is to be calculated, e.g. if its
from Monday, like so:

SELECT [EmployeeID], [FirstName], [LastName],
Anniversary([Date of Employment],2) AS Message
FROM [Employees]
SORT BY MONTH([Date of Employment]),
DAY([Date of Employment]);

Ken Sheridan
Stafford, England

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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201002/1

  #5  
Old March 8th, 2010, 05:01 AM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Calculating Age in Access

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" wrote:

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.

.

  #6  
Old March 8th, 2010, 03:09 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Calculating Age in Access

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

forest8 wrote:
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" wrote:

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.

.

  #7  
Old March 9th, 2010, 04:56 AM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Calculating Age in Access

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" wrote:

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

forest8 wrote:
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" wrote:

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.

.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.