Concanotating Date() in a form issue

  • Thread starter Thread starter RPMurphy
  • Start date Start date
R

RPMurphy

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!
 
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!
Click to expand...

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.
 
RPMurphy said:
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!
Click to expand...
=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.
 
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.
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
 
KARL said:
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.
Click to expand...

and 365.25 is not exactly correct either. Besides, Mod
**rounds** its arguments to integers before calculating its
result so you are really using xxx Mod 365.

Better to use Doug's function as Fred suggested.
 
Back
Top