Full Version: Calculating Years of Service to a future date
TZD
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
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
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
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
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
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