429,053 Members | 1,615 Online
Need help? Post your question and get tips & solutions from a community of 429,053 IT Pros & Developers. It's quick & easy.

# DateDiff Fuction

 P: n/a I am having some problem with a Year Function. I have form on which I have 4 field which indicate dates and an additional form which sums those dates: These are the fields: YEARS STARTINGDATE1 ENDINGDATE1 STARTINGDATE2 ENDINGDATE2 In each of DATE fields, I wil be adding a date. The YEARS field should sum the values I enter in the other 4 fields. I have tried simply to add the two other fields to the code below, but it does not work: =DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2]) I have also tried to use two other not visible fields, named A and, A1 and fill them with your origianal code and then add in the YEARS field: =[A]+[A1]. This seems some solution, the only problem is that it may happen that sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When this happens, it does not make to calculation. If these fields do show dates, the calculations is processed. Any further help? Thanks. Nov 12 '05 #1
4 Replies

 P: n/a > =DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] ) From MS Access 2000 Help Files: Syntax DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]) The DateDiff function only allows for calculating of _one_ pair of dates at a time. So to remedy your problem I would do the following: =(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2])) Keep in mind that you could get rounding errors of up to 2 years using such large units of mesure on each end of the + symbol. Here is an alternative solution that should yield in a smaller round error. =( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365) This second piece of code calculates the difference in days, leaving rounding errors of a day or so, then after we have added our two sets of days together we then divide by the number of days in a year (365) to get the number of years. I would give the help file a read on DateDiff, it has some very useful information for that function. HTH Mike Krous "Paolo" wrote in message news:9f**************************@posting.google.c om... I am having some problem with a Year Function. I have form on which I have 4 field which indicate dates and an additional form which sums those dates: These are the fields: YEARS STARTINGDATE1 ENDINGDATE1 STARTINGDATE2 ENDINGDATE2 In each of DATE fields, I wil be adding a date. The YEARS field should sum the values I enter in the other 4 fields. I have tried simply to add the two other fields to the code below, but it does not work: =DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] ) I have also tried to use two other not visible fields, named A and, A1 and fill them with your origianal code and then add in the YEARS field: =[A]+[A1]. This seems some solution, the only problem is that it may happen that sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When this happens, it does not make to calculation. If these fields do show dates, the calculations is processed. Any further help? Thanks. Nov 12 '05 #2

 P: n/a Note: If you use something like the second method I suggested, you may have to wrap the whole function inside the Fix() function to get rid of the decimal....Fix() is just one of many that my suite your formating needs, maybe Int() or Format() also. something like: =Fix(rest of formula here) Mike Krous "Mike Krous" wrote in message news:Y8********************@comcast.com... =DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] ) From MS Access 2000 Help Files: Syntax DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]) The DateDiff function only allows for calculating of _one_ pair of dates at a time. So to remedy your problem I would do the following: =(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2])) Keep in mind that you could get rounding errors of up to 2 years using such large units of mesure on each end of the + symbol. Here is an alternative solution that should yield in a smaller round error. =( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365) This second piece of code calculates the difference in days, leaving rounding errors of a day or so, then after we have added our two sets of days together we then divide by the number of days in a year (365) to get the number of years. I would give the help file a read on DateDiff, it has some very useful information for that function. HTH Mike Krous "Paolo" wrote in message news:9f**************************@posting.google.c om... I am having some problem with a Year Function. I have form on which I have 4 field which indicate dates and an additional form which sums those dates: These are the fields: YEARS STARTINGDATE1 ENDINGDATE1 STARTINGDATE2 ENDINGDATE2 In each of DATE fields, I wil be adding a date. The YEARS field should sum the values I enter in the other 4 fields. I have tried simply to add the two other fields to the code below, but it does not work: =DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1],[STARTINGDATE2],[ENDINGDATE2] ) I have also tried to use two other not visible fields, named A and, A1 and fill them with your origianal code and then add in the YEARS field: =[A]+[A1]. This seems some solution, the only problem is that it may happen that sometimes the STARTINGDATE2 and ENDINGDATE2 fields can be empty. When this happens, it does not make to calculation. If these fields do show dates, the calculations is processed. Any further help? Thanks. Nov 12 '05 #3