How to use iif statement in a query

[mdnikki]

I am trying to get a query to determine who in my table is past due on payments.



I have two fields which will be used to determine this. 1) Total due and 2) Installment Amount_1

So if the total due is greater than the installment payment amount that means they are in arrears.

I just can't seem to figure out how to get this to work.

I was thinking I needed an iif statement like this
Delinquent: IIf([RE_Mail_Reminder_6]![Total Due]>[RE_Mail_Reminder_6]![Installment Amount_1],"delinquent")

Do I need this to be an update query to update that field Delinquent? Not sure how this will work.

Thanks

[JoeM]

No, I don't think you want Delinquent to be a table field, you want it to be a dynamic calculation like you have set up.
Note that the IIF function has three arguments (last one, what to return if FALSE). If you do not want it to return anything if FALSE, just write it like this:

Delinquent: IIf([RE_Mail_Reminder_6]![Total Due]>[RE_Mail_Reminder_6]![Installment Amount_1],"delinquent","")

[mdnikki]

Thank you..but where does it show the return in what field? I used what is above and I just don't see where it put delinquent at all. Not in the delinquent field, not in the total due or installment amount field.

[JoeM]

You won't have a Delinquent field in your underlying table, it will be a Calculated Field in a Query.
Take a look at "Calculated Fields" or "Expressions" in Access' built-in help files if you need to read up more on it to gain a better understanding of how to enter these.

[mdnikki]

Ok I knew I wouldn't get the field in the table. I figured out why I couldn't see the calculated field, I had it named the same as an existing field. So now I changed the name of the calculated field but now I am getting a # error in that field.

[mdnikki]

Never mind, I figured it out..one of my fields was text and the other was currency, once I made them both currency the error is gone.

Thanks.