Calculating years, months, and days

[spudracer]

I'm trying to breakdown a date into years, months, and days. Simple right?

The code I've been using for months returns a negative month if it happens to be the next month. Instead of returning 1 Year, 1 Month, 23 Days, it will instead return 1 Year, -1 Month, 23 Days.

Code is:

Months: IIf(IsNull([TIR]),0,IIf(Day([TIR])<=Day(Date()),DateDiff("m",[TIR],Date())-[Years]*12,DateDiff("m",[TIR],Date())-[Years]*12-1))

What am I doing wrong?

 

[plog]

Biggest mistake is cramming it onto one line. When your code gets that complex time to make a function in a module. Move your code to a function and debugging it gets so much easier.

Also, this seems dependent on TIR and Years which I bet is its own calculation. Can you provide that code as well as some sample TIR values? Then show us what it is producing for those TIR values and what it should produce.

 

[spudracer]

Biggest mistake is cramming it onto one line. When your code gets that complex time to make a function in a module. Move your code to a function and debugging it gets so much easier.

Also, this seems dependent on TIR and Years which I bet is its own calculation. Can you provide that code as well as some sample TIR values? Then show us what it is producing for those TIR values and what it should produce.

This is all in a query.

Years is:

IIf(IsNull([TIR]),0,DateDiff("yyyy",[TIR],Date()))

Sample results:

7/1/2011 Returns 3 Year(s), 1 Month(s), 5 Day(s)
9/1/2010 Returns 4 Year(s), -1 Month(s), 5 Day(s)

 

[plog]

For 9/1/2010 your code is hitting this portion:

DateDiff("m",[TIR],Date())-[Years]*12

Which evaluates to 47-48 which evaluates to -1.


It seems your Years calculation is off. I don't think you can use DateDiff because it just subtracts year number from year number. Run this code:

TestDateDiff: DateDiff("yyyy","12/31/2010","1/1/2011")

Those dates are 1 day apart, but the return value is 1. I think you need to work from small units to larger units (days-years) and not vice versa. And of course, a custom function inside a module.

 

[spudracer]

Found a code that worked and returned what I was looking for. Never in a million years would I have thought to use code. Thanks for pointing me in the right direction!