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.

 

[Mark_]

OP,
Looking at your table definition, you may have several other issues going on.
As you have both "Service1Note" and "Service2Note", this indicates you should have a tblTicketService child table to hold your service history. This would be for normalizing your database.

You also have flags for both isPaid and PartialPayment, but I don't see an amount. Internally how are you tracking the amount of each ticket? If this amount is stored in the ticket, you can create a linking table (Child table of two parents) to link your payments to the ticket and include the amount credited for each ticket. As a child to your ticket, you could then compare the original amount of the ticket to the amount(s) credited to that ticket in payments to determine if the ticket is paid or has a partial payment. If you include the date applied in the linking table you could also know the last date of an applied payment to see if they are delinquent.