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

Thank
Hank
 
Ad

Advertisements

K

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")
 
Ad

Advertisements

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
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/


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

Thank
Hank
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top