Date calculation: help with DateDiff() syntax?

[sarajini]

Hi Tek-Tips folks!
My office has a table with information on cases. The table has fields for each case’s open date and close date. We’d like to get a new field, “TimeTaken,” with the time (in year-day-month format) between the case’s open date and close date. I know that DateDiff can help me. But I’m a newbie, and I’m having problems with the syntax.

Here’s what I ventured so far (in SQL):

SELECT CaseNumber, DateOpen, DateClose, 
Format(DateDiff(“yyyy-mm-dd”, DateOpen, DateClose)) AS TimeTaken
FROM cases_tbl;

When I tried to run it, Access asked me for the parameters of yyyy, mm, and dd. It returned a recordset with #Error listed for every record in the TimeTaken column.

There’s a little complication, one that I don’t know how to address. Every case has an open date, but not a close date. Since we haven’t closed all cases, there are many nulls in the DateClosed field. In situations where we don’t have a DateClosed, how can I ask Access to put a “n/a” in the field for TimeTaken?

Thanks so much! This is hands-down the best forum out there…
sarajini

 

[GoDawgs]

Try this:

iif(isnull([dateclose]),"N/A",DateDiff(“yyyy-mm-dd”, DateOpen, DateClose))

I think your problem is the "format" portion, you aren't actually using that, just the datediff. Hope that helps.

Kevin

 

[sarajini]

Hi Kevin/Godawgs...

The N/A bit of the code is working fine.
Unfortunately, for cases with existing close dates, I'm still being asked for year, mo, day parameters, and getting #Error in the TimeTaken column.

Hmm....

SELECT cases_tbl.CaseNumber, cases_tbl.DateOpen, cases_tbl.DateClose, 
IIf(IsNull([cases_tbl.dateclose]),"N/A",DateDiff(“yyyy-mm-dd”, DateOpen, DateClose)) AS TimeTaken
FROM cases_tbl;

thanks again,
sarajini

 

[MichaelRed]

use the ubiquitous {F1}. Topic "datediff". The first argument is documented and specified. You have other issues in the expression. It "looks-Like" you are missing an argument and / or displacing it to an incorrect placce in the string.

So, (liberally) also apply the aforementioned ubiquitous {F1} witht eh topic "Format".




MichaelRed
m.red@att.net

Searching for employment in all the wrong places