Past Due Expression


H

Hank

In a child day care center database at church, I have used the following
expression in a PaymentQry to determine a past due amount.

PastDue: IIf([DueDate]<Date(),Format(20-Nz([AmtPaid],0),"Currency"),"$0.00")
It has worked fine for several years.

The "20" in the expression represents the amount due for each payment.
Everybody's weekly payment was the same ($20.00).

Now we want to change the payment to allow a discount for a second child.
The first child would be $20 / week with siblings being charged $15 / week
each.

My thought was to add a Payment field to the PaymentTable that would be
picked up in the PaymentQry. Then I added a PmtDue text box on the
PaymentFrm. The amount due for each child would be put in the PmtDue text
box and have the PastDue amount based on the PmtDue rather than the $20 in
the query. The above expression is replaced with: PastDue:
IIf([DueDate]<Date(),Format(PmtDue-Nz([AmtPaid],0),"Currency"),"$0.00")

So far when I have tried to change the "20" to "PmtDue" I get errors. I
have totals in the from footer showing totals for "PmtDue" "AmtPaid" and
"PastDue" . When I add the "PmtDue" text box on the form I get #errors in
all the totals in the form footer. I got this error even before I added
the new "PmtDue" total in the footer.

Does this make sense? Is there a better way? Where did I go wrong?

Thanks
Hank
 
Ad

Advertisements

T

Tom van Stiphout

On Fri, 21 May 2010 06:11:12 -0500, "Hank" <[email protected]>
wrote:

The formatting code in the original expression is not necessary, and
makes it unnecessarily confusing. Format the control instead.

To me it seems the total bill for each family with daycare in this
week is:
$20 + (CountOfChildren-1) * $15
and their paymen due is that amount minus what was already paid.

The exact solution depends on the finer points of your database design
and desired report, but that is the gist of it, and it could all be
done with queries.

-Tom.
Microsoft Access MVP
 

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

Similar Threads

IIF statement 6
IIF statement failing 3
Calucation in query 2
Access Query problem 1
Query for earliest due date 5
Query not totaling correctly 3
syntax error 2
numerical summation to passed from sub to main form 2

Top