iif datediff Select Query

[sergi117]

I am setting up for a select query, a column that if the difference between two dates is exactly (down to day) 6 years apart or more then return a 1 if not then 0. I've set up an iif(datediff("y",date1column,date2column)>=2191,1, 0) but I'd like for it to be completely accurate as I know due to slight differences, leap year etc it doesn't always lead to 6 years=2191. Would anyone have a suggestion so I could have this query have more certainty in being 6 years or more apart? Thanks

[JoeM]

Here is one way:

IIf((Month([date1column])=Month([date2column])) And (Day([date1column])=Day([date2column])) And (Year([date2column])-Year([date1column])=6),1,0)

[Bob Fitz]

Sorry. Realised original suggestion will not work

[sergi117]

Apologies as I should have clarified, I meant in a situation of 6 or more years apart, but considering down to the day in difference. Your idea is great, but again by my own fault, I need the formula to not necessarily ignore dates that are past 6 years in difference.

[JoeM]

I meant in a situation of 6 or more years apart, but considering down to the day in difference. Your idea is great, but again by my own fault, I need the formula to not necessarily ignore dates that are past 6 years in difference.

Simple modification to my formula. Just change the "=6" to ">=6", i.e.

IIf((Month([date1column])=Month([date2column])) And (Day([date1column])=Day([date2column])) And (Year([date2column])-Year([date1column])>=6),1,0)

[JSR]

Simple modification to my formula. Just change the "=6" to ">=6", i.e.

IIf((Month([date1column])=Month([date2column])) And (Day([date1column])=Day([date2column])) And (Year([date2column])-Year([date1column])>=6),1,0)

That will only return dates that are exactly 6, 7, ... years apart, and nothing in between, such as 6 years and 1 day.

You want:

iif(year([date2])-year([date1])>6 or (year([date2])-year([date1])=6 and month([date2])>month([date1])) or (year([date2])-year([date1])=6 and month([date2])=month([date1]) and day([date2])>=day([date1])),1,0)

[JoeM]

That will only return dates that are exactly 6, 7, ... years apart, and nothing in between, such as 6 years and 1 day.

I think I may have misunderstood the request...
I thought that only wanted difference that were exact years apart. But in re-reading it, I think you may be correct.

[sergi117]

Thanks everybody!