On Fri, 21 May 2010 06:11:12 -0500, "Hank"
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


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