Criteria in calculated field

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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)
 
i would imagine you have spelled 'balance' wrong in one or other reference
to it - hence it cant find it
 
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?
Click to expand...

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]
 
Back
Top