YEAR Function

[Paolo]

I have a form with three fields:
STARTINGDATE
ENDINGDATE
YEARS

I would like the YEARS field to show the number of years which elaps from the
STARTINGDATE TO THE ENDINGDATE fields.

Please note that the first two fields have a mm/dd/yy format.

Any help with the Date Diff Function?

Thanks.

[Fredg]

Paolo wrote:

I have a form with three fields:
STARTINGDATE
ENDINGDATE
YEARS

I would like the YEARS field to show the number of years which elaps from the
STARTINGDATE TO THE ENDINGDATE fields.

Please note that the first two fields have a mm/dd/yy format.

Any help with the Date Diff Function?

Thanks.

Paolo,
Years = DateDiff("yyyy",[StartingDate],[EndingDate])

Note: the above will add 1 every time the year changes, whether or not a
full year has elapsed (i.e. 12/31/2002 to 1/1/2003 will = 1)

If you are looking for actual elapsed years, then use this:

In a query:
Years: DateDiff("yyyy", [StartingDate], [EndingDate]) -
IIF(Format([StartingDate], "mmdd") > Format([EndingDate], "mmdd"), 1, 0)

Directly as the control source of an unbound control:
Years=DateDiff("yyyy",[StartingDate],[EndingDate]) -
IIf(Format([StartingDate],"mmdd") > Format([EndingDate],
"mmdd"),1,0)

This Years calculation should NOT be saved to any table. Whenever you
need the result recalculate it.

--
Fred
Please reply only to this newsgroup.
I do not reply to personal email.

[Paolo]

Thanks for your help. By the way I am still having some problem with
another Year Function.
In my form I also need to add additional FROM TO fields and wanted to
know if the Date Diff function has be used.

Basically my form has 5 fields:

YEARS
STARTINGDATE1
ENDINGDATE1
STARTINGDATE2
ENDINGDATE2

In each of the the 4 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 your code 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.
Fredg <fg******@example.invalid> wrote in message news:<3y***********************@bgtnsc04-news.ops.worldnet.att.net>...

Paolo wrote:

I have a form with three fields:
STARTINGDATE
ENDINGDATE
YEARS

I would like the YEARS field to show the number of years which elaps from the
STARTINGDATE TO THE ENDINGDATE fields.

Please note that the first two fields have a mm/dd/yy format.

Any help with the Date Diff Function?

Thanks.

Paolo,
Years = DateDiff("yyyy",[StartingDate],[EndingDate])

Note: the above will add 1 every time the year changes, whether or not a
full year has elapsed (i.e. 12/31/2002 to 1/1/2003 will = 1)

If you are looking for actual elapsed years, then use this:

In a query:
Years: DateDiff("yyyy", [StartingDate], [EndingDate]) -
IIF(Format([StartingDate], "mmdd") > Format([EndingDate], "mmdd"), 1, 0)

Directly as the control source of an unbound control:
Years=DateDiff("yyyy",[StartingDate],[EndingDate]) -
IIf(Format([StartingDate],"mmdd") > Format([EndingDate],
"mmdd"),1,0)

This Years calculation should NOT be saved to any table. Whenever you
need the result recalculate it.