Date Formatting Issue

[Larry R Harrison Jr]

I have Access 2000. On a form, I want to show (in an
unbound text box) how long an agreement has been effect,
and format it in years and days.

The formula so far with this control reads:

=(Date()-[Bulk_Agreement_Start_Date])/365.25

This converts months (etc) to fractions of a year, so if
the length is 2 years 6 months, it would show up as 2.5
years.

How do I format it so it would say "2 years, 6 months" or
maybe as "30 months?"

LRH

 

[Fredg]

Larry,
For total number of months:
=DateDiff("m",[Bulk_Agreement_Start_Date],Date())
1/6/199 to 7/9/2003 will return 54.

To break it down to years and months in one control:
=Int(DateDiff("m",[Bulk_Agreement_Start_Date],Date())/12) & " year(s) & " &
DateDiff("m",[Bulk_Agreement_Start_Date],Date()) Mod 12 & " month(s)"

so 54 months will read
4 year(s) & 6 month(s)

Note: the DateDiff("m",Date1,Date2) function returns 1 for each change in
the month, not necessarily each 30 days, i.e. 1/31/2003 to 2/1/2003 is 1
month.