# Calucation in query

H

#### Hank

I use the following to calculate a past due amount in a query:

PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"currency"),"
")

What I am trying to say is if the account was due today or before, then add
the payment due (always \$25) to any fee that is due, then subtract any
amount paid, then format it as currency. So if the client pays something,
the past due amount would be the difference between what is due and what was
paid.

That seems to work fine until I get a client that is paid ahead and is not
due again until a future date. He might not be due until 4/1/09 but paid on
3/15/09. Then no amount is posted in the past due amount and my totals in
form (based on the query) footer give me an #error.

I think the problem is in the expression, but cannot figure out where. Help

Thank
Hank

K

#### Ken Snell MVP

Assuming the result from the expression should be zero when the client is

PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"currency"),"\$0.00")

H

#### Hank

Thanks Ken
That is what I needed.
Hank
Ken Snell MVP said:
Assuming the result from the expression should be zero when the client is

PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"currency"),"\$0.00")

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hank said:
I use the following to calculate a past due amount in a query:

PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"currency"),"
")

What I am trying to say is if the account was due today or before, then
add the payment due (always \$25) to any fee that is due, then subtract
any amount paid, then format it as currency. So if the client pays
something, the past due amount would be the difference between what is
due and what was paid.

That seems to work fine until I get a client that is paid ahead and is
not due again until a future date. He might not be due until 4/1/09 but
paid on 3/15/09. Then no amount is posted in the past due amount and my
totals in form (based on the query) footer give me an #error.

I think the problem is in the expression, but cannot figure out where.