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.  
Share this Question
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:
<aircode>
=(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2]))
</aircode>
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.
<aircode>
=( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) +
DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365)
</aircode>
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" <jp***@tin.it> 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.  
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" <m.krous@nospam_comcast.net> 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:
<aircode> =(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2])) </aircode>
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.
<aircode> =( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365) </aircode>
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" <jp***@tin.it> 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.
 
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" <m.krous@nospam_comcast.net> wrote in message news:<ZP********************@comcast.com>... 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" <m.krous@nospam_comcast.net> 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:
<aircode> =(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2])) </aircode>
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.
<aircode> =( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365) </aircode>
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" <jp***@tin.it> 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.
 
P: n/a

Paolo
yes this is totally possible. In my post I said to use the following: <aircode> =( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365) </aircode>
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" <jp***@tin.it> 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" <m.krous@nospam_comcast.net> wrote in message
news:<ZP********************@comcast.com>... 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" <m.krous@nospam_comcast.net> 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:
<aircode> =(DateDiff("yyyy",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("yyyy",[STARTINGDATE2],[ENDINGDATE2])) </aircode>
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.
<aircode> =( (DateDiff("y",[STARTINGDATE1],[ENDINGDATE1]) + DateDiff("y",[STARTINGDATE2],[ENDINGDATE2]) ) / 365) </aircode>
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" <jp***@tin.it> 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.
  This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 11429
 replies: 4
 date asked: Nov 12 '05
