Datediff help!

[CosmaL]

Dear friends,

hello to everyone!

I have a from and a report, which calculates the difference between 2 dates.
The fields called [Accidentdate] (the day accident happened) and [Employ Date] (the employment date).

On the same form/report, after i enter the accident date, then 3 different fields are auto calculated according to those 2 dates.

Desription below shows the calculation on each field.

My problem is that i have wrong results.

If accident date is 06/07/2016 and employ date is 01/07/2016, instead of getting
0 years
0 months
5 days
i get
0 years
1 month
25 days

Years field: =Abs(Int(DateDiff("m";[AccidentDate];[Employ Date])/12))

Months field : =Abs(IIf([DaysDiff]>=0;DateDiff("m";[AccidentDate];[Employ Date])-(Int(DateDiff("m";[AccidentDate];[Employ Date])/12)*12);DateDiff("m";[AccidentDate];[Employ Date])-(Int(DateDiff("m";[AccidentDate];[Employ Date])/12)*12)-1))

Days field: =Abs(IIf([daysdiff]>=0;DateDiff("d";DateAdd("m";DateDiff("m";[AccidentDate];[Employ Date]);[AccidentDate]);[Employ Date]);DateDiff("d";DateAdd("m";DateDiff("m";[AccidentDate];[Employ Date])-1;[AccidentDate]);[Employ Date])))

Any suggestions please? :banghead:

 

[sneuberg]

I tested this and I get the correct results if DaysDiff is greater or equal to 0 and the incorrect results if DaysDIff is negative. What's this DaysDiff field?

 

[arnelgp]

use function to make calculation simple:

Public Function DayMonthYear(s As String, d1 As Date, Optional d2 As Date = 0) As Long
    ' s         = "Days", "Months", "Years"
    ' d1        = first date
    ' d2        = second date (optional, default today's date)
    Dim ldays As Long, lmonths As Long, lyears As Long
    If d2 = 0 Then d2 = Date
    ldays = DateDiff("d", d1, d2)
    If ldays > 364 Then
        lyears = ldays \ 365
        ldays = ldays - (lyears * 365)
    End If
    If ldays > 29 Then
        lmonths = ldays \ 30
        ldays = ldays - (lmonths * 30)
    End If
    DayMonthYear = Switch(s Like "Day*", ldays, s Like "Month*", lmonths, s Like "Year*", lyears)
End Function

Day field: DayMonthYear("Day",[Employ Date], [Accident Date])
Month field: DayMonthYear("Month",[Employ Date], [Accident Date])
Year field: DayMonthYear("Year",[Employ Date], [Accident Date])

 

[missinglinq]

The problem is that DateDiff() produces a literal result...the mathematical difference between the two date components. Although only one day apart, using the 'month' interval...

DateDiff("m",#12/31/15#,#1/1/16#)

will give the result of 1 month...because, calendar-wise, they are one month apart...December and January!

Likewise, the same two dates, using the 'year' interval...

DateDiff("yyyy",#12/31/15#,#1/1/16#)

will return 1 year...because 2015 and 2016 are one 'year' apart!

Doug Steele and Graham Seach have a 'more complete' Date2Diff Function here that may work for you:

Diff2Dates

Linq ;0)>

 

[CosmaL]

arnelgp

i created the code you mentioned but the result i get is #name.
Furthermore, how can i also make it work in report?

missinglinq
I'll try it as a second test.

Thanks in advance!

 

[sneuberg]

If you used his examples as shown below note that argnelp put a space in AccidentDate.

Day field: DayMonthYear("Day",[Employ Date], [Accident Date])
Month field: DayMonthYear("Month",[Employ Date], [Accident Date])
Year field: DayMonthYear("Year",[Employ Date], [Accident Date])

Try them as

Day field: DayMonthYear("Day",[Employ Date], [AccidentDate])
Month field: DayMonthYear("Month",[Employ Date], [AccidentDate])
Year field: DayMonthYear("Year",[Employ Date], [AccidentDate])

which is the way they would appear in a query. Try

=DayMonthYear("Day",[Employ Date], [Accident Date])0
=DayMonthYear("Month",[Employ Date], [Accident Date])
=DayMonthYear("Year",[Employ Date], [Accident Date])

for textboxes. And the function he provided a has to go in a module.

But I'd give missinglinq's suggested function serious consideration. It's probably more accurate.

 

[CosmaL]

sneuberg

if i use space between accident date, it will not work, table fields are [Employ date] and [Accidentdate]

 

[arnelgp]

the code should be put in a module.
follow as sneuberg suggest.

if you are going to use in query as expression:

Day: DayMonthYear("Day",[Employ Date], [AccidentDate])

if rowsource of textbox (both in form or report):

=DayMonthYear("Day",[Employ Date], [AccidentDate])

 

[sneuberg]

sneuberg

Sorry. bad copy and paste. That should be


Try

=DayMonthYear("Day",[Employ Date], [AccidentDate])
=DayMonthYear("Month",[Employ Date], [AccidentDate])
=DayMonthYear("Year",[Employ Date], [AccidentDate])

for textboxes. And the function he provided a has to go in a module.

 

[CosmaL]

Guys,
i've tried it again, with sneuberg's suggestions but no result again.
You can see above screenshots from the project.

Module photo: http://prntscr.com/btzws4
Form design: http://prntscr.com/btzwzb
Form with data: http://prntscr.com/btzx60

In greek, i have to use ; instead of ,

The locked field is [employ date]

 

[sneuberg]

The screen shot of the Form Design shows a space in [AccidentDate]

Try:

=DayMonthYear("Day",[Employ Date], [AccidentDate])
=DayMonthYear("Month",[Employ Date], [AccidentDate])
=DayMonthYear("Year",[Employ Date], [AccidentDate])

 

[CosmaL]

Sorry, copy paste error.

I've changed it, still the same problem.....

Form design: http://prntscr.com/bu0598
Foram data: http://prntscr.com/bu05eu

 

[sneuberg]

I've tried to simulate you situation in the attached database. Maybe it's not working on your system because of the dd/mm/yyyy date format. Please see if the attached database works on your system. I've included the missinglinq's suggested function in this for comparision. If it works on your system you will see different results for the second record 18 vs 20 days.

arnelgp's function appears to being adding extra days because of leap years.

 

[CosmaL]

sneuberg,

i copied the functions and code from the database you uploaded and it's working fine!
I used arnelgp's method.

Thank you very much!!!!!!!!!!!!