AVG(IIF with multiple criteria

[Moxioron]

Hello all.

I am trying to perform an Average IF calculation in a query with multiple criteria.

This is what I came up with:

A+AvgBal>1: Avg(IIf([qry_Cardholder Data for RePrice Through Oct 2012]![FICO 0512] Between 730 And 9000,[qry_Cardholder Data for RePrice Through Oct 2012]![Balance]>0,[Balance],0,))

So basically, average [Balance] if [FICO 0512] is between 730 and 9000 and [Balance] is greater than zero

I am getting 'the expression you entered has a function containing the wrong number of arguments.

Where am I going astray? Thank you.

 

[boblarson]

You can't use AVG for this. But you can use this:

...
 
A+AvgBal>1: Sum(IIf([qry_Cardholder Data for RePrice Through Oct 2012]![FICO 0512] Between 730 And 9000 And [qry_Cardholder Data for RePrice Through Oct 2012]![Balance]>0,[Balance],0))/IIf(Sum(IIf([qry_Cardholder Data for RePrice Through Oct 2012]![FICO 0512] Between 730 And 9000 And [qry_Cardholder Data for RePrice Through Oct 2012]![Balance]>0,1,0))>0,Sum(IIf([qry_Cardholder Data for RePrice Through Oct 2012]![FICO 0512] Between 730 And 9000 And [qry_Cardholder Data for RePrice Through Oct 2012]![Balance]>0,1,0)),1)
 
...

 

[Moxioron]

Thank you for responding.

However, I am getting a Data type mismatch in criteria expression error message.

 

[boblarson]

Thank you for responding.

However, I am getting a Data type mismatch in criteria expression error message.

What's the datatype of both of your fields? I assumed number. Also, if you have any NULLS you probably will need to put NZ functions in around each of the field names in the expression (remember it would need to go around the actual field, not the Sum or IIF statements. So like
Nz([Balance],0)

 

[Moxioron]

Worked. Thank you sir.

 

[boblarson]

Worked. Thank you sir.

No problem. It is quite the complex expression and would have been harder to describe how to do it than to just do it.