Null dates? (1 Viewer)

[legendv]

in a query how do you get a current usage of days if there is a start date and no end date? such as...

if enddate is null then
date() - beginingdate
else
enddate - beginingdate
endif

 

[Rich]

=IIf(IsDate([EndDate]),DateDiff("d",[EndDate],[BeginigDate]),DateDiff([BeginingDate],Date()))

 

[legendv]

Rich,
Thanks, but what is the "IsDate"?
Is that what is checking to see if date is present?
I think I might understand the flow! But it didn't work in A2K is what you wrote 97 specific?
And is it proper to place in a query?

 

[David R]

Rich may have been in a bit of a hurry. Try this slight correction:
IIf(IsDate([EndDate]),DateDiff("d",[EndDate],[BeginningDate]),DateDiff("d",[BeginningDate],Date()))

Assuming your field names are correct, this should work in Acc2k.

 

[legendv]

modification worked with this

IIf(IsDate([enddate]),DateDiff("d",[enddate],[startdate]),DateDiff("d",[startdate],Date()))

and worked if startdate entered and no enddate, but when enddate entered value is correct except that its a negative number?

 

[legendv]

IIf(IsDate([enddate]),DateDiff("d",[startdate],[enddate]),DateDiff("d",[startdate],Date()))

that worked! Thanks, to both, and David, thanks for answering my other one. This forum is a lifesaver!!!!!!!! And quite educational!

much appreciation.