Calculate Years of Service

[Jeff Kaufman]

I am currently usins Microsoft Access 2003 and am having a little trouble
writing a query to show the years of service a employee has with the company.
Below is an example of my current query that I would like to just add
another field too for "Years of Service" where it take todays date and the
hire date and calculate the difference between the two and simply return the
number of years of service. I would like for the query to continue to have
the function that allows me to sort only by those agents that have
anniversary dates in certain months as the current query does.


SELECT Employee_HireDate_Qry.Manager, Employee_HireDate_Qry.TeamName,
Employee_HireDate_Qry.[Employee Name], Employee_HireDate_Qry.HireDate,
Employee_HireDate_Qry.[E-Mail]
FROM Employee_HireDate_Qry
WHERE (((Employee_HireDate_Qry.HireMonth)=[Enter digit of month you want to
see]))
ORDER BY Employee_HireDate_Qry.HireMonth, Employee_HireDate_Qry.HireDay,
Employee_HireDate_Qry.HireYear;

 

[Dorian]

First, you need to tell us how 'years of service' must be calculated?
Does it have to be complete years or can it be partial years?
Can someone carry over previous years service if the leave and later rejoin
the com pany?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[fredg]

I am currently usins Microsoft Access 2003 and am having a little trouble
writing a query to show the years of service a employee has with the company.
Below is an example of my current query that I would like to just add
another field too for "Years of Service" where it take todays date and the
hire date and calculate the difference between the two and simply return the
number of years of service. I would like for the query to continue to have
the function that allows me to sort only by those agents that have
anniversary dates in certain months as the current query does.

SELECT Employee_HireDate_Qry.Manager, Employee_HireDate_Qry.TeamName,
Employee_HireDate_Qry.[Employee Name], Employee_HireDate_Qry.HireDate,
Employee_HireDate_Qry.[E-Mail]
FROM Employee_HireDate_Qry
WHERE (((Employee_HireDate_Qry.HireMonth)=[Enter digit of month you want to
see]))
ORDER BY Employee_HireDate_Qry.HireMonth, Employee_HireDate_Qry.HireDay,
Employee_HireDate_Qry.HireYear;

Add a new column to your query (in the QBE grid).

YearsOfService: DateDiff("yyyy",[HireDate],Date())
-IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0)

 

[Jeff Kaufman]

I attempted to just add the below into the field row in the design view of my
query and I get an error message everytime. What is a QBE Grid?

Thanks

I am currently usins Microsoft Access 2003 and am having a little trouble
writing a query to show the years of service a employee has with the company.
Below is an example of my current query that I would like to just add
another field too for "Years of Service" where it take todays date and the
hire date and calculate the difference between the two and simply return the
number of years of service. I would like for the query to continue to have
the function that allows me to sort only by those agents that have
anniversary dates in certain months as the current query does.

SELECT Employee_HireDate_Qry.Manager, Employee_HireDate_Qry.TeamName,
Employee_HireDate_Qry.[Employee Name], Employee_HireDate_Qry.HireDate,
Employee_HireDate_Qry.[E-Mail]
FROM Employee_HireDate_Qry
WHERE (((Employee_HireDate_Qry.HireMonth)=[Enter digit of month you want to
see]))
ORDER BY Employee_HireDate_Qry.HireMonth, Employee_HireDate_Qry.HireDay,
Employee_HireDate_Qry.HireYear;

Add a new column to your query (in the QBE grid).

YearsOfService: DateDiff("yyyy",[HireDate],Date())
-IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0)

 

[Jeff Kaufman]

Years of service is simply calculated by taking today's date and subtracting
their hire date... so if someone was hired on August 6th of 2008 their Years
of service would be 0 until August 6th of 2009 where then it would change to
1 year and on August 6th of 2010 it woudl be 2 Years.

Thanks

First, you need to tell us how 'years of service' must be calculated?
Does it have to be complete years or can it be partial years?
Can someone carry over previous years service if the leave and later rejoin
the com pany?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

I am currently usins Microsoft Access 2003 and am having a little trouble
writing a query to show the years of service a employee has with the company.
Below is an example of my current query that I would like to just add
another field too for "Years of Service" where it take todays date and the
hire date and calculate the difference between the two and simply return the
number of years of service. I would like for the query to continue to have
the function that allows me to sort only by those agents that have
anniversary dates in certain months as the current query does.


SELECT Employee_HireDate_Qry.Manager, Employee_HireDate_Qry.TeamName,
Employee_HireDate_Qry.[Employee Name], Employee_HireDate_Qry.HireDate,
Employee_HireDate_Qry.[E-Mail]
FROM Employee_HireDate_Qry
WHERE (((Employee_HireDate_Qry.HireMonth)=[Enter digit of month you want to
see]))
ORDER BY Employee_HireDate_Qry.HireMonth, Employee_HireDate_Qry.HireDay,
Employee_HireDate_Qry.HireYear;

 

[John W. Vinson]

I attempted to just add the below into the field row in the design view of my
query and I get an error message everytime. What is a QBE Grid?

The "QBE Grid" *is* the design view of the query.

Put the expression into the first vacant cell in the Field row of the grid.

 

[Jeff Kaufman]

Okay when I do that I get the following error message:
The function you entered can't be used in this expression
*You may have used a DoEvents, LBound, UBound, Spc, or Tab function in an
expression
*You may have used an aggregate function, such as Count, in a design grod or
in a calculated control field.

Thanks

Jeff

 

[fredg]

Okay when I do that I get the following error message:
The function you entered can't be used in this expression
*You may have used a DoEvents, LBound, UBound, Spc, or Tab function in an
expression
*You may have used an aggregate function, such as Count, in a design grod or
in a calculated control field.

Thanks

Jeff

Telling us what you did is not as helpful as showing us the entire
query SQL. Copy and Paste the actual SQL into a reply message.

 

[Jeff Kaufman]

Here is the current SQL of the query...

SELECT Employee_HireDate_Qry.Manager, Employee_HireDate_Qry.TeamName,
Employee_HireDate_Qry.[Employee Name], Employee_HireDate_Qry.HireDate,
Employee_HireDate_Qry.[E-Mail]
FROM Employee_HireDate_Qry
WHERE (((Employee_HireDate_Qry.HireMonth)=[Enter digit of month you want to
see]))
ORDER BY Employee_HireDate_Qry.HireMonth, Employee_HireDate_Qry.HireDay,
Employee_HireDate_Qry.HireYear;

 

[fredg]

Here is the current SQL of the query...

SELECT Employee_HireDate_Qry.Manager, Employee_HireDate_Qry.TeamName,
Employee_HireDate_Qry.[Employee Name], Employee_HireDate_Qry.HireDate,
Employee_HireDate_Qry.[E-Mail]
FROM Employee_HireDate_Qry
WHERE (((Employee_HireDate_Qry.HireMonth)=[Enter digit of month you want to
see]))
ORDER BY Employee_HireDate_Qry.HireMonth, Employee_HireDate_Qry.HireDay,
Employee_HireDate_Qry.HireYear;

Telling us what you did is not as helpful as showing us the entire
query SQL. Copy and Paste the actual SQL into a reply message.

And where is that YearsOf Service column that you wish to add to the
query that is the purpose of this thread?

Let's get rid of some of the clutter.
No need to sort by Month, Day, and Year fields as you are only
displaying the one selected month's data. Just sort by [HireDate].
Try your SQL this way.

SELECT Manager, TeamName, [Employee Name], HireDate,
[E-Mail], DateDiff("yyyy",[HireDate],Date())-IIf(Format([HireDate],
"mmdd")>Format(Date(),"mmdd"),1,0) as YearsOfService
FROM Employee_HireDate_Qry
WHERE Month(HireMonth) = [Enter digit of month you want to see]
ORDER BY [HireDate];

Access will then add the table names and parenthesis when you save the
query.

 

[Jeff Kaufman]

Okay... I copied your SQL into Access and I am getting the following error
message:
Function is not available in expressions in query expression
'DateDiff("yyyy",[HireDate],Date())-IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0)'

SELECT Manager, TeamName, [Employee Name], HireDate,
[E-Mail], DateDiff("yyyy",[HireDate],Date())-IIf(Format([HireDate],
"mmdd")>Format(Date(),"mmdd"),1,0) as YearsOfService
FROM Employee_HireDate_Qry
WHERE Month(HireMonth) = [Enter digit of month you want to see]
ORDER BY [HireDate];

Here is the current SQL of the query...

SELECT Employee_HireDate_Qry.Manager, Employee_HireDate_Qry.TeamName,
Employee_HireDate_Qry.[Employee Name], Employee_HireDate_Qry.HireDate,
Employee_HireDate_Qry.[E-Mail]
FROM Employee_HireDate_Qry
WHERE (((Employee_HireDate_Qry.HireMonth)=[Enter digit of month you want to
see]))
ORDER BY Employee_HireDate_Qry.HireMonth, Employee_HireDate_Qry.HireDay,
Employee_HireDate_Qry.HireYear;

On Thu, 6 Aug 2009 06:30:01 -0700, Jeff Kaufman wrote:

Okay when I do that I get the following error message:
The function you entered can't be used in this expression
*You may have used a DoEvents, LBound, UBound, Spc, or Tab function in an
expression
*You may have used an aggregate function, such as Count, in a design grod or
in a calculated control field.

Thanks

Jeff

:

On Wed, 5 Aug 2009 12:14:07 -0700, Jeff Kaufman

I attempted to just add the below into the field row in the design view of my
query and I get an error message everytime. What is a QBE Grid?

The "QBE Grid" *is* the design view of the query.

Put the expression into the first vacant cell in the Field row of the grid.
--

John W. Vinson [MVP]


Telling us what you did is not as helpful as showing us the entire
query SQL. Copy and Paste the actual SQL into a reply message.

And where is that YearsOf Service column that you wish to add to the
query that is the purpose of this thread?

Let's get rid of some of the clutter.
No need to sort by Month, Day, and Year fields as you are only
displaying the one selected month's data. Just sort by [HireDate].
Try your SQL this way.

SELECT Manager, TeamName, [Employee Name], HireDate,
[E-Mail], DateDiff("yyyy",[HireDate],Date())-IIf(Format([HireDate],
"mmdd")>Format(Date(),"mmdd"),1,0) as YearsOfService
FROM Employee_HireDate_Qry
WHERE Month(HireMonth) = [Enter digit of month you want to see]
ORDER BY [HireDate];

Access will then add the table names and parenthesis when you save the
query.

 

[John W. Vinson]

Function is not available in expressions in query expression
'DateDiff("yyyy",[HireDate],Date())-IIf(Format([HireDate],"mmdd")>Format(Date(),"mmdd"),1,0)'

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.