IIF with 2 conditions

[sheckay]

Hello. Can someone help me with a simple IIF statement with 2 conditions? I just have an unbound text box with the following in its control source:

=IIf([ispaid]=Yes And [Balance]>0,"NOT PAID IN FULL!","")

All I want is for NOT PAID IN FULL! to populate the text box if the isPaid check box is checked and the balance text box is greater than 0. When I only have one condition, balance greater than 0, it works. But not with 2 conditions. However, Access doesn't have a problem with accepting the code.
Is the problem that isPaid is a field on the main form and balance (and the host text box) are on the subform of the main form?
I've tried a bunch of things. Nothing's worked.
Thanks in advance!

 

[Gasman]

I would expect IsPaid to be boolean so 0/-1, or False/True?

Debug.Print IsPaid and see what it actually is, not what you *think* it is.

 

[sheckay]

I would expect IsPaid to be boolean so 0/-1, or False/True?

Debug.Print IsPaid and see what it actually is, not what you *think* it is.

Thank you for your reply. I tried changing the criteria to 1 and -1, to no avail. I'm currently looking into the debug.print recommendation you've made. I've never done that before. That's probably the info that I need.

 

[Gasman]

You could try

=IIf(ispaid And Balance > 0,"NOT PAID IN FULL!","")

I would inspect both control's values.

 

[sheckay]

You could try

=IIf(ispaid And Balance > 0,"NOT PAID IN FULL!","")

I would inspect both control's values.

Right now, since I can't figure out how to do the debug.print, I'm focusing on setting the IIF to one condition. But that condition is set to the IsPaid field. I thought this might be a good start:
=IIf([IsPaid]<>0,"NOT PAID!","")
But it's not working either. Neither is:
=IIf([IsPaid]<>False,"NOT PAID!","")
=IIf([IsPaid]<>Off,"NOT PAID!","")
Do I have to be more specific with the field reference? [formname]![fieldname]?

 

[arnelgp]

what is the Datatype of IsPaid field? Is it Yes/No field or Short Text?

 

[sheckay]

what is the Datatype of IsPaid field? Is it Yes/No field or Short Text?

Yes/No

 

[sheckay]

I've also tried:
=IIf([IsPaid]>0,"NOT PAID!","")
and
=IIf([IsPaid]<0,"NOT PAID!","")
same result

 

[sheckay]

=IIf([IsPaid]=0,"NOT PAID!","") too
Same result

 

[Gasman]

Yes/No

Did you try my code in post #4 ?
See the links in my signature for debugging.

 

[MajP]

Do I have to be more specific with the field reference? [formname]![fieldname]?

No, not if calling from the same form.

Yes/No

Did you double check at the table level? From what you show I doubt it is boolean. Although the values may only show data that looks boolean (True, False, Yes, No). The field may not actually be boolean. It could be a text field.
Can you post an example?

FYI, things that resolve to True or False like expressions, you do not have to say
If True = True, or if True = False

You an simply do
If True

Example
IF [IsPaid] = True then...
is the same as
If [IsPaid] then

If isnull([somefield]) = False becomes
if not isnull([somefield]) then

 

[sheckay]

Did you try my code in post #4 ?
See the links in my signature for debugging.

Yes, I did. Sorry. Same result.

 

[Gasman]

Yes, I did. Sorry. Same result.

Well I would have to say IsPaid is not boolean.
Show us a picture of the table field definitions.

 

[arnelgp]

try, if IsPaid on Mainform, use the Expression on the Subform:

=IIf(Forms![MainFormName]![ispaid]=-1 And [Balance]>0,"NOT PAID IN FULL!","")

if the textbox on MainForm:

=IIf([ispaid]=-1 And Forms!MainForm!Subform.Form![Balance]>0,"NOT PAID IN FULL!","")

 

[sheckay]

No, not if calling from the same form.

Did you double check at the table level? From what you show I doubt it is boolean. Although the values may only show data that looks boolean (True, False, Yes, No). The field may not actually be boolean. It could be a text field.
Can you post an example?

FYI, things that resolve to True or False like expressions, you do not have to say
If True = True, or if True = False

You an simply do
If True

Example
IF [IsPaid] = True then...
is the same as
If [IsPaid] then

If isnull([somefield]) = False becomes
if not isnull([somefield]) then

Thank you. I tried:
=IIf([ispaid],"NOT PAID IN FULL!","")
same result

 

[sheckay]

try, if IsPaid on Mainform, use the Expression on the Subform:

=IIf(Forms![MainFormName]![ispaid]=-1 And [Balance]>0,"NOT PAID IN FULL!","")

if the textbox on MainForm:

=IIf([ispaid]=-1 And Forms!MainForm!Subform.Form![Balance]>0,"NOT PAID IN FULL!","")

Thank you!!! That did it!! I had to put the main form info in there. I tried that before even posting this question. But my code was off a little, Thank you!!

 

[Gasman]

Do you have Option Explicit at the top of every module, as that should not have compiled.
You do compile your code, do you not?

 

[MajP]

Do you have Option Explicit at the top of every module, as that should not have compiled.
You do compile your code, do you not?

If it is in an expression of a calculated control it would compile. That happens at runtime.
It starts with = so that has to be an expression.

 

[sheckay]

Do you have Option Explicit at the top of every module, as that should not have compiled.
You do compile your code, do you not?

I wasn't compiling. Just running the code behind it through testing out database functionality.

 

[Pat Hartman]

=IIf([ispaid]=Yes And [Balance]>0,"NOT PAID IN FULL!","")

In what form is the code running? Both fields are referenced as if they are on the form where the code is running. If one of the fields is on a different form, you need to adjust your reference. For example:

=IIf(Forms!frmParent![ispaid]=True And [Balance]>0,"NOT PAID IN FULL!","")

ALSO = YES may be considered a string in some versions of Access whereas True is always a Boolean so either use the word True without quotes or use -1 if the BE is Jet/ACE or 1 if the BE is SQL Server. Given this anomaly, True is the safer option since Access will translate it correctly.

And finally, this is why we don't store the same data in multiple places. You should always be calculating the value for IsPaid by summing the values of the child records and never store this value.