Solved

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

Posted on 2011-03-03
3
311 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 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