Nested SUM

[fenhow]

Hi, I am trying to use a SUM method inside of an IIF Statement.

Status: IIf([ScheduleT].[AmountDue]=[PaymentMade],"Current",IIf((Now())<[ScheduleT].[DueDate],"Not Yet Due",IIf((Now()),pastdue([ScheduleT].[DueDate]),"")))

I want the IIf([ScheduleT].[AmountDue]=[PaymentMade] to be IIf([ScheduleT].[AmountDue]=Sum([PaymentMade]), "Current" etc..

But when I run it I get a Cannot have aggregate function inside GROUP BY clase error. I tried to create the SUM value in a new expression and call that expression but then I get Your query does not include the specified expression *** as part of an aggregate function..

Is there any way to do this?

Thanks.
Fen

 

[plog]

Is there any way to do this?

I am sure there is, but I can't help you get there from here. What I would need is sample data to demonstrate the issue. I would need 2 sets:

A. Starting sample data from your table(s). Give me the relevant table and field names of the pertinent data along with enough sample data to cover all cases.

B. Expected results of A. Based on the sample data you provide in A, show me what you expect your query to produce.