Query problem - "You tried to execute..."

[LB79]

Hello,

I wonder if someone can help me tweek this query?

When I run the query it tells me:

[SIZE=3][COLOR=#000080][FONT=Arial]You tried to execute a query that doesn’t include the specified expression: [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]TargetTotal: IIf([Target Amount]=0,0,Format(Sum([Target Amount]),"###,###,###"))[/FONT][/COLOR][/SIZE]

Although I do have 0s in the Target Amount table, the query brings them up as blanks which is why I've inserted the iif statement.

Thanks )

 

[spaddhu]

I think you left out the main part of the error - "... in an aggregate expression"

This is a common error of grouping. Also your IIF looks a bit complicated.

Try this thing instead of what you have given.

TargetTotal: Format(Sum(nz([Target Amount])),"##,##0"))
Ensure that for the above field the 'value' in 'Total' row of the design view is 'Expression'.

 

[LB79]

Thanks Spaddhu! Thats great!

 

[spaddhu]

I assume that this means you got it working ??
Happy for being able to help...