Calucation in query

[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
Please.

Thank
Hank

 

[Ken Snell MVP]

Assuming the result from the expression should be zero when the client is
paid ahead:

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

 

[Hank]

Thanks Ken
That is what I needed.
Hank

Assuming the result from the expression should be zero when the client is
paid ahead:

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/

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 Please.

Thank
Hank