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.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


"Larry R Harrison Jr" <larrytucaz@yahoo.com> wrote in message
news:9a8401c34637$4b2cd200$a401280a@phx.gbl...

> 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