IIF statement issue..... 1

[chainedtodesk]

i am trying to execute the following "IIF" statement but get errors, i have a calc to figure out number of days a ticket is open. then there is a ct field that i need to add to that, but sometimes there isnt a ct and its null, so i thought i should be able to add the iff statement that if the CT is null then just use the days calc otherwise add the two together, which is what the original field in the query did except it dropped data where CT was null... i have tried this over and over and get the error about trying to execute a query that doesnt include........ and so on.

thanks

NewTotal: IIf(Avg([Warranty Shipment CT]) Is Null,Avg(DateDiff("d",DateValue([StartedToNew].[ChangedDateTime]),DateValue([NewToAuth].[ChangedDateTime]))),Avg(DateDiff("d",DateValue([StartedToNew].[ChangedDateTime]),DateValue([NewToAuth].[ChangedDateTime]))+Avg([Warranty Shipment CT])))

 

[Andrzejek]

If you use: [blue][tt]NZ(CT, 0)[/tt] [/blue]then wherever your CT is NULL it will take a value of 0
This will not affect your SUM or Average in your calculations.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[chainedtodesk]

thanks Andy, that was the answer worked like a champ. thanks much