Avg on Calc'd Field containing If Statement

[cabbey77]

Here's the story. Created a Query (single table)with Begin Date and End date parameters. Have a Completion Date field and a Request Received Date field.

In the Report I created two calculated fields. One for turn time Subtracting the Completion Date field from the Request Rcvd Date Field. This works fine. The second field calculates an age for incomplete fields using an If statement to determine if the completion date is null, then using the date diff function again. Again this works fine.

The report is grouped by Request Rcvd Date by Year and again by Month.

I want to average the above two calculated fields for the month, the year, and the report time frame. It will actually allow this to occur on the turntime field, but will not calculate the age field with a "too complex" error.

I have tried calculating in my query and then referencing these fields for the average calculations with the same results.

Any ideas? I am copying and pasting my calculated fields below. (These are from the query, they are much less ugly if performed in the report)

Days_To_Completion: DateDiff("d",[tblInventory_Item_Request_Metric_Data]![Request_Recvd_Dt],[tblInventory_Item_Request_Metric_Data]![Date-of_Completion])
DaysOld: IIf((IsNull([tblInventory_Item_Request_Metric_Data]![Date-of_Completion])),(DateDiff("d",[tblInventory_Item_Request_Metric_Data]![Request_Recvd_Dt],Now())),"")

 

[Golom]

Your DaysOld calculation has some extra sets of parentheses that it doesn't need. It will also return (potentially) different data types. Specifically, a numeric from DateDiff if the first argument is NULL and an empty string if it is not. Should it return "0" (the number) as the third argument?

I also note that the calculation contains

Date[COLOR=red]-[/color]of_Completion

should that be

Date[COLOR=red]_[/color]of_Completion

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[cabbey77]

Wouldnt a "0" return skew the average calculation? And yes it should, but that is incorrect in the table and will be corrected. I dont see that it should be causing any issues.

 

[Golom]

We would need to see the complete SQL to be able to say what the impact on the average calculation would be. I suspect however that an empty string will not have the desired effect either.

The real issue though is that every entry in a column must be of the same data type and you have a statement that may produce different data types in the same column.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[cabbey77]

SELECT tblInventory_Item_Request_Metric_Data.Request_Recvd_Dt, tblInventory_Item_Request_Metric_Data.Part_Number, tblInventory_Item_Request_Metric_Data.First_Response_Date, tblInventory_Item_Request_Metric_Data.[Date-of_Completion], tblInventory_Item_Request_Metric_Data.[Rejected/Approved], DateDiff("d",[tblInventory_Item_Request_Metric_Data]![Request_Recvd_Dt],[tblInventory_Item_Request_Metric_Data]![Date-of_Completion]) AS Days_To_Completion, IIf(IsNull([tblInventory_Item_Request_Metric_Data]![Date-of_Completion]),DateDiff("d",[tblInventory_Item_Request_Metric_Data]![Request_Recvd_Dt],Now()),"") AS DaysOld
FROM tblInventory_Item_Request_Metric_Data
WHERE (((tblInventory_Item_Request_Metric_Data.Request_Recvd_Dt) Between [Enter Begin Date] And [Enter End Date]));

 

[Golom]

Try this

PARAMETERS [Enter Begin Date] Date, [Enter End Date] Date;
SELECT 
Request_Recvd_Dt, 
Part_Number, 
First_Response_Date, 
[Date-of_Completion], 
[Rejected/Approved], 

DateDiff("d", [Request_Recvd_Dt],[Date-of_Completion]) 
    AS Days_To_Completion, 

IIf(IsNull([Date-of_Completion]),DateDiff("d",[Request_Recvd_Dt],Now()),NULL) 
    AS DaysOld


FROM [tblInventory_Item_Request_Metric_Data] As T


WHERE [Request_Recvd_Dt] Between [Enter Begin Date] And [Enter End Date]

You must be computing the average elsewhere. I don't see it in this SQL.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[cabbey77]

Perfect! Thank you!

And yes, the averages are computed in the report itself, and they now work!

You are my hero for the day!

I do have one lingering question. I understand everything in your code except why it is necessary to put in Paramaters when you have a Where statement.

 

[Golom]

It's one of those "... are you paranoid enough things ...". SQL usually gets it right in dynamically generating parameter prompts but sometimes, particularly with datetime fields, it doesn't. The PARAMETERS statement just removes any possible confusion about what sort of type conversion to do.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]