Datediff()

[st1300]

Hey folks,

I would like to use the Datediff() function to do the below (which works) but I would like to add a twist but have yet to solve.

IIf(DateDiff("m",[tblService2].[CompletionDate],Now())>3,"Yes","NO")



What I'm hoping to do is do the above but have it "do something" a week before the 3 months are reached. So, I thought of the ww interval but that could get messy as I would like to keep the month interval.

I've not yet started testing with the ww to see how it actually works but I assume it counts the # off weeks between date1 and date2.

[aytee111]

if completion date = Date()-7

[boblarson]

First off, DateDiff using months will return a 1 even if you were comparing between November 30th and December 1st. So, the question is do you care? But if you do, then you might want to use days instead (because weeks will also give you a 1 if used between the last day of the month and the first day of the next month if they are on a different week).

IIf(DateDiff("d",[tblService2].[CompletionDate], Now()) > 83 And < 90, "Do Something", IIf(DateDiff("d",[tblService2].[CompletionDate], Now()) >=90, "Yes", "NO"))

[st1300]

First off, DateDiff using months will return a 1 even if you were comparing between November 30th and December 1st. So, the question is do you care? But if you do, then you might want to use days instead (because weeks will also give you a 1 if used between the last day of the month and the first day of the next month if they are on a different week).

IIf(DateDiff("d",[tblService2].[CompletionDate], Now()) > 83 And < 90, "Do Something", IIf(DateDiff("d",[tblService2].[CompletionDate], Now()) >=90, "Yes", "NO"))

Funny, I was just coming to the day realization be not on paper so to speak but in my mind. So, yes going to put the day idea in motion.

Thanks.

[st1300]

Funny, I was just coming to the day realization be not on paper so to speak but in my mind. So, yes going to put the day idea in motion.

Thanks.

Hmm sytax error with comma, but what?

IIf(DateDiff("d", [tblService2].[CompletionDate], Now()) > 83 And < 90, "Yes","NO")

Only thing I'm wondering is the second argument if you will is not the yes no of the iif ?? If that is how one would say it.

[boblarson]

Oops, my bad. You would need to change it to:

IIf(DateDiff("d", [tblService2].[CompletionDate], Now()) > 83 And DateDiff("d", [tblService2].[CompletionDate], Now())< 90,"Yes","NO")

[st1300]

Oops, my bad. You would need to change it to:

IIf(DateDiff("d", [tblService2].[CompletionDate], Now()) > 83 And DateDiff("d", [tblService2].[CompletionDate], Now())< 90,"Yes","NO")

Hmm always evaluates to NO. I wonder if the AND is causing the iif to get confused?

Ok, I think I see what is going on now. The evaluate state is between 83 and 90 and anything oustside of this will go to NO, which is exactly what we asked of it. Not were I want to go but close. Some tweaking needed.

Yes, that is it (above line). Working as should.

Thanks.