DateDiff results

[CEV]

Hi, I have the following in my query:

Expr1:
DateDiff('m',[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])

How can I make the results show "##Years, ##Months, ##Days" for each record?
I would like it to show "1Year, 5Days" for someone that has worked for 370
days.

Thanks,

CEV

 

[fredg]

Hi, I have the following in my query:

Expr1:
DateDiff('m',[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])

How can I make the results show "##Years, ##Months, ##Days" for each record?
I would like it to show "1Year, 5Days" for someone that has worked for 370
days.

Thanks,

CEV

Check "A More Complete DateDiff Function" at

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

 

[Guest]

Try this but check the double quotes after pasting --

DateDiff("yyyy",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])
&
IIf(DateDiff("yyyy",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])=1,"
year, "," years, ") &
DateDiff('m',[tblEmployees]![DateofHire],DateAdd("yyyy",-DateDiff("yyyy",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency]),[tblEmployees]![DateLeftAgency]))
& " months, " & DateDiff('d',[tblEmployees]![DateofHire],
DateAdd("m",-DateDiff("m",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency]),[tblEmployees]![DateLeftAgency])) & " days"

 

[CEV]

That is exactly what I am trying to accomplish. There must be more to it
than just adding "ymd" to the expression because I get an error when I do
that. I am not up to par on Code so is there a place I can just copy and
paste the code listed there? Would there be anything I need to change inside
the code?

Thanks,

CEV

Hi, I have the following in my query:

Expr1:
DateDiff('m',[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])

How can I make the results show "##Years, ##Months, ##Days" for each
record?
I would like it to show "1Year, 5Days" for someone that has worked for
370
days.

Thanks,

CEV

Check "A More Complete DateDiff Function" at

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

 

[CEV]

This is getting closer. It is showing the info but it is incorrect.

DateofHire is 12/20/04 and DateLeftAgency is 4/7/06 and the answer it gives
me is: 2 years, -8 months, -13 days

It looks like it is calculating the indivual numbers. There must be a way to
correct this?

Thanks,

CEV

Try this but check the double quotes after pasting --

DateDiff("yyyy",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])
&
IIf(DateDiff("yyyy",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])=1,"
year, "," years, ") &
DateDiff('m',[tblEmployees]![DateofHire],DateAdd("yyyy",-DateDiff("yyyy",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency]),[tblEmployees]![DateLeftAgency]))
& " months, " & DateDiff('d',[tblEmployees]![DateofHire],
DateAdd("m",-DateDiff("m",[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency]),[tblEmployees]![DateLeftAgency]))
& " days"

Hi, I have the following in my query:

Expr1:
DateDiff('m',[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])

How can I make the results show "##Years, ##Months, ##Days" for each
record?
I would like it to show "1Year, 5Days" for someone that has worked for
370
days.

Thanks,

CEV

 

[CEV]

Thank You very much Fred. The more complex these databases get, the more I
am learning. I did finally figure this out. I copied and pasted the code
into a new module and substituted Date1 and Date2 for the names of the
fields I have. There is a more complex query that I am currently working on
but I will post a seperate post for that one. If I can get help with that
one, I believe it will help me out alot with others I need as well.

Thanks,

CEV

Hi, I have the following in my query:

Expr1:
DateDiff('m',[tblEmployees]![DateofHire],[tblEmployees]![DateLeftAgency])

How can I make the results show "##Years, ##Months, ##Days" for each
record?
I would like it to show "1Year, 5Days" for someone that has worked for
370
days.

Thanks,

CEV

Check "A More Complete DateDiff Function" at

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