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.

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 gseach@pacificdb.com.au
'           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/Forums.aspx/access-gettingstarted/201002/1

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.
> 

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/Forums.aspx/access-gettingstarted/201002/1

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.
> > 
> .
> 

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.
>>>
>> .
>>

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.
> >>>
> >> .
> >>
> .
>