By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 429,053 Members | 1,615 Online + Ask a Question
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
Share this Question
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

 P: n/a Thanks Mike, I am using yuor second code (/365) and works fine. There is by the way still one thing that I would like to ask. It may happen that sometimes I will not complete the STARTINGDATE2 and ENDINGDATE2 fields but just the first two ones therofore I would like my YEARS field to be able to sum only just the first two fields. The way I wanted is that the YEARS field should always show the date difference between the STARTINGDATE1 and ENDINDATE1 fields, then, if I will complete the STARTINGDATE2 and ENDINGDATE2 fields, well then the difference should be summed to the first result. Is this possible? Thanks "Mike Krous" wrote in message news:... 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 #4

 P: n/a Paolo- yes this is totally possible. In my post I said to use the following:=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365) In order to seperate that information better I would do the following: In your query I would seperate the formula above into two formulas then add them together later. column1 - get diff of starting date1, and ending date1, wrapped the function inside of a Nz() function, the Nz() function will make sure that I at least get a 0 for my calculation if for some odd reason I would have gotten a null. This will make sure Ive got something to add to later. =Nz(DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]),0) column2 - get the diff of starting date 2 and ending date 2. =Nz(DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]),0) column3 - add column 1 and column 2 together then divide by 365 to get total years. =((column1 + column2) / 365) Note: columns 1 and 2 are still in day format. when you display them in a control you could use the following to get back to years: =column1 / 365 or column2 / 365 everytime you see column1 and column2 you will have to replace the words "column1" and "column2" with your actual names. Alternatively you could get the time in years from start1 end1 and start2 end2 by using the following additional columns in your query. column4 =column1 / 365 column5 =column2 / 365 then you can simply place column 4 and 5 into your form without any calculations. now with the above columns you have everything broke out into pieces. Column1 holds the difference between start1 and end1 and column2 holds the difference between start2 and end2, column3 simply has the total between the two differences divided by 365 to get years. The added columns 4 and 5 simply take columns 1 and 2 and bring them down into years. HTH Mike Krous "Paolo" wrote in message news:9f**************************@posting.google.c om... Thanks Mike, I am using yuor second code (/365) and works fine. There is by the way still one thing that I would like to ask. It may happen that sometimes I will not complete the STARTINGDATE2 and ENDINGDATE2 fields but just the first two ones therofore I would like my YEARS field to be able to sum only just the first two fields. The way I wanted is that the YEARS field should always show the date difference between the STARTINGDATE1 and ENDINDATE1 fields, then, if I will complete the STARTINGDATE2 and ENDINGDATE2 fields, well then the difference should be summed to the first result. Is this possible? Thanks "Mike Krous" wrote in message news:... 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 #5

### This discussion thread is closed

Replies have been disabled for this discussion. 