If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Past Due Expression
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 |
#2
|
|||
|
|||
Past Due Expression
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 |
Thread Tools | |
Display Modes | |
|
|