IIf / And formula

[andydarly]

Hi, all.

I am trying to write a formula for query builder and it's close but not quite right... I have a table with effectively calls answered and another with calls abandoned within them. I have taken what I need from those tables and discarded what I don't need to give me dates, interval periods etc for answered and another for abandoned. all pretty straightforward so far...

Now what I am looking to do is to merge those two tables and everything works fine except for the Abandoned numbers within the query based on the original two queries being slightly out.

The fields in the original queries are named SumOfInteractions Answered by Agent for Calls Answered and Total Abandoned Calls for Abandoned. There is another field called Interaction Type that will either have the value Inbound or Outbound.

I basically want the formula I am trying to write say that if the Calls Answered and Abandoned are both zero don't enter anything, if the Calls Answered is zero and Abandoned > zero then put the Abandoned, if Calls Answered is greater than zero and Abandoned is zero, set abandoned to zero (or blank), if Calls Answered and Abandoned are both greater than zero then put abandoned. All of these need to be under the condition that if Interaction Type is Outbound then Abandoned Calls is zero (or blank) and to only enter the value for Abandoned if Interaction Type has the value Inbound.

I really hope that makes sense. the Calls Answered field has its own field in the new query and the numbers coming out of that are fine so I am not currently concerned with that. The code I have come up with is the below but like I say it isn't quite there...


Abandoned Calls: Sum(IIf([SumOfInteractions Answered by Agent] Is Null And [Total Abandoned Calls] Is Null,0,IIf([Interaction Type]="Outbound",0,[Total Abandoned Calls])))


Am I woefully off? I am really quite new to Access so if you could give me the kind of answer even an idiot would understand then I would be very grateful.

thanks in advance.

Andy

[orange]

Welcome to the forum.

Show your table(s) design and the SQL of the query(s). We need to see some details.

[andydarly]

Hi, not sure how to show table design or what I would include but the SQL of the query is


SELECT qry_IB_Interval_Ans_1617Q3.[Yr & Wk], qry_IB_Interval_Ans_1617Q3.Date, qry_IB_Interval_Ans_1617Q3.[Time Slice 30min], qry_IB_Interval_Ans_1617Q3.[Service Group Level 1 (SG1)], qry_IB_Interval_Ans_1617Q3.[Service Group Level 2 (SG2)], qry_IB_Interval_Ans_1617Q3.[Service Group Level 3 (SG3)], qry_IB_Interval_Ans_1617Q3.[Service Call Type (SCT)], Sum(IIf([SumOfInteractions Answered by Agent] Is Null,0,[SumOfInteractions Answered by Agent])+IIf([Total Abandoned Calls] Is Null,0,[Total Abandoned Calls])) AS CallsOffered, Sum(qry_IB_Interval_Ans_1617Q3.[SumOfInteractions Answered by Agent]) AS [SumOfSumOfInteractions Answered by Agent], Sum(IIf([SumOfInteractions Answered by Agent] Is Null And [Total Abandoned Calls] Is Null,0,IIf([Interaction Type]="Outbound",0,[Total Abandoned Calls]))) AS [Abandoned Calls], Sum(qry_IB_Interval_Ans_1617Q3.[SumOfTotal Handle Time]) AS [SumOfSumOfTotal Handle Time], Sum(qry_IB_Interval_Ans_1617Q3.[SumOfAgent Total Talk Duration]) AS [SumOfSumOfAgent Total Talk Duration], Sum(qry_IB_Interval_Ans_1617Q3.[SumOfHold Duration]) AS [SumOfSumOfHold Duration], Sum(qry_IB_Interval_Ans_1617Q3.[SumOfTotal ACW Duration]) AS [SumOfSumOfTotal ACW Duration], Sum(qry_IB_Interval_Ans_1617Q3.[SumOfIVR Duration]) AS [SumOfSumOfIVR Duration], qry_IB_Interval_Ans_1617Q3.[Interaction Type]
FROM qry_IB_Interval_Ans_1617Q3 LEFT JOIN qry_IB_Interval_Abandoned_1617Q3 ON (qry_IB_Interval_Ans_1617Q3.[Time Slice 30min] = qry_IB_Interval_Abandoned_1617Q3.[Timeslice 30 Mins]) AND (qry_IB_Interval_Ans_1617Q3.[Service Call Type (SCT)] = qry_IB_Interval_Abandoned_1617Q3.[Service Call Type (SCT)]) AND (qry_IB_Interval_Ans_1617Q3.Date = qry_IB_Interval_Abandoned_1617Q3.Date)
GROUP BY qry_IB_Interval_Ans_1617Q3.[Yr & Wk], qry_IB_Interval_Ans_1617Q3.Date, qry_IB_Interval_Ans_1617Q3.[Time Slice 30min], qry_IB_Interval_Ans_1617Q3.[Service Group Level 1 (SG1)], qry_IB_Interval_Ans_1617Q3.[Service Group Level 2 (SG2)], qry_IB_Interval_Ans_1617Q3.[Service Group Level 3 (SG3)], qry_IB_Interval_Ans_1617Q3.[Service Call Type (SCT)], qry_IB_Interval_Ans_1617Q3.[Interaction Type]
HAVING (((qry_IB_Interval_Ans_1617Q3.Date)=#3/6/2017#))
ORDER BY qry_IB_Interval_Ans_1617Q3.Date, qry_IB_Interval_Ans_1617Q3.[Time Slice 30min];


Does that help?

many thanks

Andy

[aytee111]

If interaction type = "inbound" then use what is in the abandoned field otherwise it is zero/blank.

[orange]

No, the long sql doesn't help me, but it may be helpful to others.
Your sql revolves around a query qry_IB_Interval_Ans_1617Q3
which we know nothing about.

Do you have anything in your relationships window? Database Tools-->Relationships

[andydarly]

Hi, thanks for this. I get exactly the same results as the original formula though

[aytee111]

What is the problem?

[NTC]

Abandoned Calls: IIf([IsNull([SumOfInteractions Answered by Agent]) AND IsNull([Total Abandoned Calls]),0,IIf([Interaction Type]="Outbound",0,[Total Abandoned Calls]))

[andydarly]

Hi, thanks for that.

Not sure if there is something missing but I get the error "The expression you entered has a function containing the wrong number of arguments".

Not sure if its a comma, a bracket, the order...I just don't know enough

[NTC]

there was a left [ before the first IsNull in error

Abandoned Calls: IIf(IsNull([SumOfInteractions Answered by Agent]) AND IsNull([Total Abandoned Calls]),0,IIf([Interaction Type]="Outbound",0,[Total Abandoned Calls]))