• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

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.


  • 3
  • 2
1 Solution
Rey Obrero (Capricorn1)Commented:

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


=IIf([OctPayStatus] <> 0 And [OctPayment]>0,"Paid","Unpaid")
bobmillingtonAuthor Commented:
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.
bobmillingtonAuthor Commented:
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.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Jeffrey CoachmanMIS LiasonCommented:
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)


Rey Obrero (Capricorn1)Commented:
< Also, the control box tells me of a cirular reference.>

try renaming your controls.. for textboxes  add "txt" in front..
if the current name of the control is "Unpaid", change the name to "txtUnpaid" (without the quotes )
bobmillingtonAuthor Commented:
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")
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now