Need help with the "IF" command Please

[Remeartificer]

Need help with the "IF" command Please

I have a table called "tblTasks"

In this table I have two fields: "Status" and "PaymentDate"

My aim is to acheive the following: If the field for payment date is left empty then the status will read "OPEN", but if there is a date entered then status will read "CLOSED".

The data is displayed on a subform within a form in datasheet view. In the edit mode I have entered an unbound text box but I dont know what IF command to write.

I am sure this is a simple "IF" command but I cant get my head around it. Can someone please assist?

Thank you.

 

[Remou]

Something like:
[tt]=IIf([PaymentDate] Is Null, "Open", "Closed")[/tt]

 

[Remeartificer]

Many thanks for your reply Remou. May I ask another question now I have seen your solution?

The person I am setting this up for has now said that occasionally he makes part-payments, which would mean that the account wouldnt be closed if only half was paid.

I would now need a formula that would recognise if the amount he paid was the same as the quote. ie: the field "PaidAmount" is the same as the field "Form2QuoteAmount".

How could I reconfigure =IIf([PaymentDate] Is Null, "Open", "Closed") to do this?

Apologies for the last minute change to the question and thanks again.

 

[Remeartificer]

Would it be simply:

=IIf([PaidAmount]=[Form2QuoteAmount],"Open","Closed")

?

 

[Remeartificer]

Revised:

=IIf([PaidAmount]=[Form2QuoteAmount],"Closed","Open")

I think this is working.....

 

[Remeartificer]

Yep, happy that this is working. But now want to add to it:

So far I have a status showing OPEN, CLOSED. But I now want to add PENDING.

So any amount under the quote amount, but not zero, needs to change the status to PENDING.

Any suggestions?

 

[Remeartificer]

I need something like

>0 but not = [Form2QuoteAmount], "Pending",

But I also need to inc the open & closed part.

Please help

 

[ItIsHardToProgram]

Try something like this.

If paidamount = Form2QuoteAmount then
     Paidamount = "Closed"
Elseif Nz(paidamount) <> 0 then
     Paidamount = "Pending"
Else
     Paidamount = "Open"
End if

You can add criterias or commands if u like.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Remou]

On one line:
[tt]IIf(Nz([Form2QuoteAmount])-Nz([PaidAmount])=0,"Closed",IIf(Nz([Form2QuoteAmount])-Nz([PaidAmount])=Nz([Form2QuoteAmount]),"Open","Pending"))[/tt]