DateDiff

[mphynson44]

I have a business and I have cases for clients. Each case has a Due date and a completed date. I want to use the DateDiff expression to tell me how many of my cases exceeded their due date. So I wanted to use the DateDiff and then set a criteria of ">1" to represent all cases that went "past due".

Any help would be greatly appreciated!

Here is what I have so far:



TRANSFORM DateDiff("d",[Due Date],[Date Completed]) AS Expr2


SELECT ClosedAdHoc.[Case Number], Count(ClosedAdHoc.[Case Number]) AS [Total Of Case Number]
FROM ClosedAdHoc
WHERE (((DatePart("yyyy",[Date Completed]))=2013))
GROUP BY ClosedAdHoc.[Case Number]
PIVOT Format([Date Completed],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

[June7]

What is the issue? Error message, wrong results, nothing?

[mphynson44]

Thanks for the reply, sorry should have been more descriptive. Error, "You tried to execute a query that does not include the specified expression 'DateDiff..... as part of an aggregate function"

[June7]

A CROSSTAB requires 3 fields.

Need a Value set to an aggregate function on the Total row.

Change Group By under the DateDiff expression to Where and put >1 on the Criteria row.

Pick some field (is there an ID) to do Count on and set as Value.