Average of calculated conditional field

[dmg197243]

I have a calculated field on the detail level of a report which is calculated as follows:

=Iif([BREAK]>1200 AND [LUNCH]<>0),1800-[BREAK],IIF([BREAK]<>0,900-[BREAK],0))

This calculates correctly, however when I put the following calculated field in the Group footer section of the report, I receive a "statement too complex" error message:

=1800-Avg(Iif([BREAK]>1200 AND [LUNCH]<>0),1800-[BREAK],IIF([BREAK]<>0,900-[BREAK],0))

The calculation for the Group footer works in Access 97 so I know that syntactically the statement is correct.

Can anyone tell me why this will not work in Access 2003 and provide a work around so I can get the Average of the calculated Detail section field.

Thank you.

 

[maxhugen]

Instead of calculating the first expression you listed above in the report, do it in the underlying query.

Then you'll be able to used this calculated field for the avg() calc in the report.

HTH

Max Hugen
Australia

 

[dmg197243]

Thanks. When I put it in the query all I get returned is an "#Error" for the value. If I remove the conditions and just put in something like "[BREAK]-1800" then it works in either the query or the report. For some reason Access 2003 doesn't seem to like doing an AVG (or SUM or any aggregate function) on a conditional expression.

This whole thing works fine in Access 97, but is giving me a headache in 2003.

Any further suggestions?

 

[dhookom]

IIf() returns a variant data type. You might want to try wrap the IIf()s in Val() like:
=1800-Avg(Val(Iif([BREAK]>1200 AND [LUNCH]<>0),1800-[BREAK],IIF([BREAK]<>0,900-[BREAK],0)))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[maxhugen]

Are you saying that your first expression:

=Iif([BREAK]>1200 AND [LUNCH]<>0),1800-[BREAK],IIF([BREAK]<>0,900-[BREAK],0))

won't work?

It sounds like you are using your second expression which does the Avg() in the query - and no, that's not going to work. You would need to use the the avg in the report.

So if you named the calculated field something like 'BreakCalc' in the query, in the report you would use it in the form:

=1800-Avg(BreakCalc)



Max Hugen
Australia

 

[maxhugen]

BTW, looks like there's a typo in your expression:

=Iif([BREAK]>1200 AND [LUNCH]<>0[red])[/red], 1800-[BREAK], IIF([BREAK]<>0,900-[BREAK],0))

s/b:

=Iif([BREAK]>1200 AND [LUNCH]<>0, 1800-[BREAK], IIF([BREAK]<>0,900-[BREAK],0))


Max Hugen
Australia

 

[dmg197243]

Thanks.

The first statement is in the report and it does work (the typo was just a typo in this thread, not in the actual code).

It is when I try to put a field in the footer that does the Avg(... first statement...) that returns the "expression too complex" message.

 

[dhookom]

BTW: did you ever try to put the expression into the report's record source query? If so, does it appear left or right aligned in the datasheet view. My guess is left aligned. If so, did you try wrapping the working expression in Val() and then try to Avg()?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[maxhugen]

dmg197243,
I think you may not have understood (or I didn't explain very well), but that expression should not be in the report, but in the query as a calculated field.

So, in the query builder, one of the fields returned by the query should look something like:

BreakCalc: Iif([BREAK]>1200 AND [LUNCH]<>0, 1800-[BREAK], IIF([BREAK]<>0,900-[BREAK],0))

In the report, you will be able to do:

=1800-Avg(BreakCalc)

Max Hugen
Australia