Conditional DateDiff

[amy3000]

I'm doing a datediff where I'm using a persons birthday and the day they were closed to our services to determine age. However, if they haven't been closed in 2002 I want to use 12/31/2002 to substitute for CloseDate.


I found this example:
IIf(IsNull([DateOut]) = True, DateDiff("dd", [DateIn], Date()), DateDiff("dd", [DateIn], [DateOut])) As TimeInProgram

In the following thread:
thread181-405046

I get the #error in the field. Here's how I have it. I probably have it set up incorrectly:

IIf(IsNull([CloseDate])=True,DateDiff("d",[DOB],#12/30/2002#,DateDiff("d",[CloseDate],[DOB])))

 

[JeremyNYC]

Hmm. You have reversed the position of DOB in the true and false parts. That will flip the sign on the difference. Don't know if that matters to you, or if it was even intentional.

Getting to the reason for the error: you don't have an = at the start of this. If that's just what you pasted here then it's not the problem, but if you don't have an = in the Control Source field for this control you'll get an error.

But that would end up being the #Name error, not #Error.

OK, you've got a misplaced ). Kill one at the end and put it after the second #.

It won't cause an error, but you don't need the = True bit, because IsNull evaluates to True or False on its own. So here's another version that I think will work for you:
=IIf(IsNull([DueDate]),DateDiff("d",[CreationDate],#12/30/02#),DateDiff("d",[DueDate],[CreationDate]))

though you may want to flip the values in one of the two DateDiffs.

Hope this helps.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[amy3000]

Thanks, that did the trick. Amy