calculate two dates

S

Spier2vb

pls., help me to creat a query to calculate between two dates

i have Employee Table three fields 1- for hire-date-day
2- for hire-date-month
3- for hire-date-year
i need to calculate the experience date with current date and hire date

example:
current date 30/11/2008
hire date 29/10/2000
the experience 1 - 1 - 8
 
J

John Spencer

I think you are trying to calculate the years, months, and days of
experience based on the difference between the current date and the hire
date. If that is true the easiest method I know is to use a custom VBA
function developed by Graham Seach and Doug Steele. You will have to
download/copy the function and paste it into a VBA module in your
application.

Then all you would need to do is to call it with
Diff2Dates("ymd",[Hire Date],Date())

Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

For example:

?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
4 years
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
4 years 25 days
?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
4 years 0 months 25 days
?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
1486 days

?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours
?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
42 hours 47 minutes 33 seconds
?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
1 day 18 hours 47 minutes 33 seconds

?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
1 day
?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
-1 day
?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
1 day


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Make query to calculate two dates 2
calculate two dates 1
Number of days between 2 dates 7
Date function in a query 2
Add years between two dates 4
Calendar Date 4
Availability between dates 4
IIf statement 4

Top