Criteria in calculated field

[Guest]

Hopefully this is a simple solution. I have a calculated field as follows:

Amt Due: IIf([Balance]>0 And [Total]>=[Feb 90],[Balance],0)

The [Balance] field is also a calculed field.

What I want to do is set the criteria for the Amt Due to be greater than
zero (0). However, when I try this I get the error message that "The
Microsoft Jet Engine does not recognize [Balance] as a valid field name or
expression."

Does anyone have any ideas on what I need to do?

 

[Phil]

Try replacing [Balance] with the calculation.

For instance if [Balance] is [Total]-[Payments]

then change
IIf([Balance]>0 And [Total]>=[Feb 90],[Balance],0)
to
IIf(([Total]-[Payments])>0 And [Total]>=[Feb 90],([Total]-[Payments]),0)

 

[JethroUK©]

i would imagine you have spelled 'balance' wrong in one or other reference
to it - hence it cant find it

 

[John Vinson]

Hopefully this is a simple solution. I have a calculated field as follows:

Amt Due: IIf([Balance]>0 And [Total]>=[Feb 90],[Balance],0)

The [Balance] field is also a calculed field.

What I want to do is set the criteria for the Amt Due to be greater than
zero (0). However, when I try this I get the error message that "The
Microsoft Jet Engine does not recognize [Balance] as a valid field name or
expression."

Does anyone have any ideas on what I need to do?

You need to recapitulate the expression which calculates [Balance] in
the IIF. You often cannot reuse a calculated field in a further
calculation.

John W. Vinson[MVP]