Criteria in calculated field


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?
 
Ad

Advertisements

P

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)
 
J

JethroUK©

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

Advertisements

J

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top