asked on

Access 2010 (Iff Statement)

I have the following iff statement.

=IIf(([OctPayStatus]="0") And ([OctPayment]>"0"),"Unpaid","Paid")

OctPayStatus is a checkbox and OctPayment is a numeric value. I am trying to get the following answered: If the OctPayStatus is not checked and OctPayment has a value greater than 0 then show Unpaid else show paid.

Access tells me I have a circular reference on the control property......

A little push would be greatly appreciated.

Cheers,

Bob

Rey Obrero (Capricorn1)

try

=IIf([OctPayStatus]=0 And [OctPayment]>0,"Unpaid","Paid")

or

=IIf([OctPayStatus] <> 0 And [OctPayment]>0,"Paid","Unpaid")

bobmillington

ASKER

Ok, tried this code as sugguested..

=IIf([OctPayStatus]=0 And [OctPayment]>0,"Unpaid","Paid")

It returns the Paid records ok, but the unpaids show #Type! where "Unpaid" should be. Also, the control box tells me of a cirular reference.

I dont understand it. I tried this code =IIf([OctPayStatus]=0,"Unpaid","Paid") and it shows the right values......its when I add the and part it fails.

bobmillington

ASKER

Looks like the problem is with the OctPayment field. I tried

=IIf([OctPayment]=0,"No Value","Value")

and I get the same #type! error. The OctPayment field is a currency field....just doesn't make sense.

Jeffrey Coachman

OctPayment may be NULL
(Null is not the same as zero)

So try
=IIf(IsNull([OctPayment]),"No Value","Value")

...as a test

If this is the issue, I am sure capricorn1 can get you sorted the rest of the way...
(so no points wanted for me)

;-)

JeffCoachman

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bobmillington

ASKER

I just put txt in front of the control name and it magically worked. The syntax in the expression builder was good afterall.

Many Thanks!!!!!

=IIf(([OctPayStatus]=0 And [OctPayment])<>0,[OctPayment],"0")