Solved

How do I use a begin date field to calculate total years up to now?

Posted on 2011-03-03
3
312 Views
Last Modified: 2012-05-11
I have two begin date fields.  i want to use the total years up to now in each separate field and make a list of years to select from to locate only people with so many years in each field.  
0
Comment
Question by:tlahpalli
  • 2
3 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 35031812
run this query, change field names and table accordingly and post your comment

select [Name], [datefield],datediff("yyyy",[datefield],Date()) as noOfYears
from table x
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35031843
if you want more accurate year calculation, use this

SELECT [Name], HireDate, DateDiff("yyyy",[HireDate],Date())+(Date()<DateSerial(Year(Date()),Month([HireDate]),Day([HireDate]))) AS YearsOfService
FROM tblEmployees
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35038750
If you want it absolutely correct, you need to take into account those born on 29th Feb. Use this function:
Public Function AgeSimple( _
  ByVal datDateOfBirth As Date) _
  As Integer

' Returns the difference in full years from datDateOfBirth to current date.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
' After an idea of Markus G. Fischer.
'
' 2007-06-26. Cactus Data ApS, CPH.

  Dim datToday  As Date
  Dim intAge    As Integer
  Dim intYears  As Integer
    
  datToday = Date
  ' Find difference in calendar years.
  intYears = DateDiff("yyyy", datDateOfBirth, datToday)
  If intYears > 0 Then
    ' Decrease by 1 if current date is earlier than birthday of current year
    ' using DateDiff to ignore a time portion of datDateOfBirth.
    intAge = intYears - Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) > 0)
  End If
  
  AgeSimple = intAge
  
End Function

Open in new window


Then your query will look something like this:

Select
  *,
  AgeSimple([YourDateField1], Date()) As ExperienceOne,
  AgeSimple([YourDateField2], Date()) As ExperienceTwo
From
  tblYourTable
Where
  AgeSimple([YourDateField1], Date()) >= [Years of First Experience]
  And
  AgeSimple([YourDateField2], Date()) >= [Years of Second Experience];

When you run the query, it will ask for the Years of Experience for the two fields.

/gustav
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question