Full Version: Calculating Years of Service to a future date
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
TZD
Oct 12 2006, 02:56 PM
Working on an employee database I have a field call [HireDate].

I am trying to setup a query which prompts for a BegMonth and an EndMonth. I want to use the first day for the BegMonth and the last day for the EndMonth so I can calculate YearsOfService based on the last day of the EndMonth.

I would like the end user to just enter a month for these two prompts, such as "09" for Sept 01, Current Year (BegMonth) and "12" for Dec 31, Current Year (EndMonth).

So far the criteria I am using works as far as showing the correct employees with Employment Anniversaries between the entered criteria; however, for the YearsOf Service Calculation it is calculating the YearsOfService to the System Date.

Any ideas how I can change the calculation to use the last day of the EndMonth rather than the System Date?

Here is whatI am current using to calculate YearsOfService:
Years: Fix(DateDiff("d",[Master List].[Date of Hire],Date())/365)

Thanks
ScottGem
Oct 12 2006, 03:06 PM
Do a search here on AGE, you will find several ways of calculating age. You can easily adapte them to your needs. The method you are using is not as accurate.
r_cubed
Oct 12 2006, 06:01 PM
The following 2 functions can be used to calcualte the (respective) FIRST and LAST date of the month for ANY date that is passed to it ..... please note that the optional parameters in BOTH functions were added to my original functions for specific purposes on the current application that I have just completed, and in the main (and for you) they can be ignored.

CODE
Function FirstOfMonth(InputDate As Variant, _
                      Optional ReturnAsNumeric As Boolean = False, _
                      Optional ReversedOrder As Boolean = False) _
              As Variant
              
'  Return a date that is the first day of the month of the date passed
Dim d As Integer, m As Integer, Y As Integer

    If IsNull(InputDate) Then
        FirstOfMonth = Null
    Else
        d = 1
        m = Month(InputDate)
        Y = Year(InputDate)
        
        If ReturnAsNumeric = False _
          And ReversedOrder = False Then
            FirstOfMonth = DateSerial(Y, m, d)
        Else
            If ReturnAsNumeric = False Then
                FirstOfMonth = Y & "/" & Format(m, "00") & "/" & Format(d, "00")
            Else
                If ReversedOrder = False Then
                    FirstOfMonth = Format(d, "00") & Format(m, "00") & Y
                Else
                    FirstOfMonth = Y & Format(m, "00") & Format(d, "00")
                End If
            End If
        End If
    End If
    
End Function
'
'
Function LastOfMonth(InputDate As Variant, _
                      Optional ReturnAsNumeric As Boolean = False, _
                      Optional ReversedOrder As Boolean = False) _
              As Variant
'  Return a date that is the last day of the month of the date passed
Dim d As Integer, m As Integer, Y As Integer
Dim WorkDate As Date
    
    If IsNull(InputDate) Then
        LastOfMonth = Null
    Else
        d = Day(InputDate)
        m = Month(InputDate)
        Y = Year(InputDate)
        'find the first day of next month, then back up one day
        WorkDate = DateAdd("m", 1, DateSerial(Y, m, 1)) - 1
        
        d = Day(WorkDate)
        m = Month(WorkDate)
        Y = Year(WorkDate)
        
        If ReturnAsNumeric = False _
          And ReversedOrder = False Then
            LastOfMonth = DateSerial(Y, m, d)
        Else
            If ReturnAsNumeric = False Then
                LastOfMonth = Y & "/" & Format(m, "00") & "/" & Format(d, "00")
            Else
                If ReversedOrder = False Then
                    LastOfMonth = Format(d, "00") & Format(m, "00") & Y
                Else
                    LastOfMonth = Y & Format(m, "00") & Format(d, "00")
                End If
            End If
        End If
    End If
End Function


** As Scott has also suggested the method that you are using to calculate 'Age / Years of Service" is NOT correct ....
ScottGem
Oct 13 2006, 07:29 AM
I didn't read this thoroughly enough.

The expression: DateSerial(Year(Date()),EndMonth-1,0)

will produce the last day of whatever month (in the current year) is entered as EndMonth. For Beginning Month use:

DateSerial(Year(Date()),BegMonth,1)
TZD
Oct 13 2006, 11:47 AM
Thanks for your replies, I am trying to incoporate your suggestions to my application. I was using a statement in my query to get the beginning and ending month with a statement such as :

Between [Enter Begginning Date] And [Enter Ending Date]

I also have a format on this column: Format([HireDate],"mm")

But I believe I will need to abondon the idea above and have two separate columns in the query, one for BegDate, and one for EndDate and then use the DateSerial Function.


If I use the Function by r_Cube I would put these functions in a module, correct?
ScottGem
Oct 13 2006, 01:12 PM
I'm not clear here on what you are searching for. Are you trying to find the years of Service for employees between a range of dates? If so, Add a column to your query:

CurrYrSvce: DateSerial(Year(Date()),Month([DOH]),Day([DOH]))

In THAT column use your BETWEEN clause as criteria.

You CAN do what you want with the months by using a Form to supply criteria. Have two hidden controls on the form and have Option groups to select the Beg and End month. Set the ControlSource of the two hidden controls to the DateSerial expressions I gave you. Then set the crtieria for the CurrYrSvce coloum to:

BETWEEN Forms!formname!txtBeg AND Forms!formname!txtEnd
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.