Prev: GoToRecord on a subform...
Next: lost focus
From: RPMurphy on 2 Sep 2009 13:16
I have a form with the following text fields:

StartDate
EndDate
Total

The StartDate field uses a DatePicker, the EndDate has Date() in it's
SourceControl property. I am using this code in the Total SourceControl
property:

=DatePart("yyyy",Date())-DatePart("yyyy",[4StartDate]) & "y " &
Abs(DatePart("m",Date())-DatePart("m",[4StartDate])) & "m " &
Abs(DatePart("d",Date())-DatePart("d",[4StartDate])) & "d "

This one works, kind of, 9/27/2006 - 9/2/2009 shows a difference of 3y 0m
25d when it should
read as 2y 11m 5d...?

Can anyone shed some light on what I am missing or doing wrong? Thanks!

From: fredg on 2 Sep 2009 13:48
On Wed, 2 Sep 2009 10:16:07 -0700, RPMurphy wrote:

> I have a form with the following text fields:
>
> StartDate
> EndDate
> Total
>
> The StartDate field uses a DatePicker, the EndDate has Date() in it's
> SourceControl property. I am using this code in the Total SourceControl
> property:
>
> =DatePart("yyyy",Date())-DatePart("yyyy",[4StartDate]) & "y " &
> Abs(DatePart("m",Date())-DatePart("m",[4StartDate])) & "m " &
> Abs(DatePart("d",Date())-DatePart("d",[4StartDate])) & "d "
>
> This one works, kind of, 9/27/2006 - 9/2/2009 shows a difference of 3y 0m
> 25d when it should
> read as 2y 11m 5d...?
>
> Can anyone shed some light on what I am missing or doing wrong? Thanks!

See:
http://www.accessmvp.com/djsteele/Diff2Dates.html

to accurately return Year, Month, and Date in one function.


= Diff2Dates("ymd",[StartDate],[EndDate],True)

You do realize, I hope, that this value ought not to be saved in any
table.
Any time you need the results of the calculation, re-calculate it, on
a form or report, as above.


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
From: KARL DEWEY on 2 Sep 2009 13:56
Have you thought about using DateDiff?
DateDiff("d", [StartDate], Date()) \365.25 & "y " & (DateDiff("d",
[StartDate], Date()) Mod 365.25) \30 & "m " & ((DateDiff("d", [StartDate],
Date()) Mod 365.25) Mod 30) & "d"

It will not be exactly correct as not all months have 30 days.

--
Build a little, test a little.


"RPMurphy" wrote:

> I have a form with the following text fields:
>
> StartDate
> EndDate
> Total
>
> The StartDate field uses a DatePicker, the EndDate has Date() in it's
> SourceControl property. I am using this code in the Total SourceControl
> property:
>
> =DatePart("yyyy",Date())-DatePart("yyyy",[4StartDate]) & "y " &
> Abs(DatePart("m",Date())-DatePart("m",[4StartDate])) & "m " &
> Abs(DatePart("d",Date())-DatePart("d",[4StartDate])) & "d "
>
> This one works, kind of, 9/27/2006 - 9/2/2009 shows a difference of 3y 0m
> 25d when it should
> read as 2y 11m 5d...?
>
> Can anyone shed some light on what I am missing or doing wrong? Thanks!
>
From: RPMurphy on 2 Sep 2009 14:17
Karl Dewey, you are the MAN!!!! Worked like a charm once I changed the name
of the fields. Think you just saved me several hours of pain, thank you so
much!

Ray

"KARL DEWEY" wrote:

> Have you thought about using DateDiff?
> DateDiff("d", [StartDate], Date()) \365.25 & "y " & (DateDiff("d",
> [StartDate], Date()) Mod 365.25) \30 & "m " & ((DateDiff("d", [StartDate],
> Date()) Mod 365.25) Mod 30) & "d"
>
> It will not be exactly correct as not all months have 30 days.
>
> --
> Build a little, test a little.
>
>
> "RPMurphy" wrote:
>
> > I have a form with the following text fields:
> >
> > StartDate
> > EndDate
> > Total
> >
> > The StartDate field uses a DatePicker, the EndDate has Date() in it's
> > SourceControl property. I am using this code in the Total SourceControl
> > property:
> >
> > =DatePart("yyyy",Date())-DatePart("yyyy",[4StartDate]) & "y " &
> > Abs(DatePart("m",Date())-DatePart("m",[4StartDate])) & "m " &
> > Abs(DatePart("d",Date())-DatePart("d",[4StartDate])) & "d "
> >
> > This one works, kind of, 9/27/2006 - 9/2/2009 shows a difference of 3y 0m
> > 25d when it should
> > read as 2y 11m 5d...?
> >
> > Can anyone shed some light on what I am missing or doing wrong? Thanks!
> >
From: Mike Painter on 2 Sep 2009 13:50
RPMurphy wrote:
> I have a form with the following text fields:
>
> StartDate
> EndDate
> Total
>
> The StartDate field uses a DatePicker, the EndDate has Date() in it's
> SourceControl property. I am using this code in the Total
> SourceControl property:
>
> =DatePart("yyyy",Date())-DatePart("yyyy",[4StartDate]) & "y " &
> Abs(DatePart("m",Date())-DatePart("m",[4StartDate])) & "m " &
> Abs(DatePart("d",Date())-DatePart("d",[4StartDate])) & "d "
>
> This one works, kind of, 9/27/2006 - 9/2/2009 shows a difference of
> 3y 0m 25d when it should
> read as 2y 11m 5d...?
>
> Can anyone shed some light on what I am missing or doing wrong?
> Thanks!
=DatePart("yyyy",Date())-DatePart("yyyy",[4StartDate]) = 2009 - 2006 = 3
Abs(DatePart("m",Date())-DatePart("m",[4StartDate])) =9-9 = 0
Abs(DatePart("d",Date())-DatePart("d",[4StartDate]))=27-2 = 25
You are not treating the date as a date but as (decimal) numbers

http://www.accessmvp.com/djsteele/Diff2Dates.html gives a complete method.


 | 
Pages: 1
Prev: GoToRecord on a subform...
Next: lost focus