A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Past Due Expression



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2010, 12:11 PM posted to microsoft.public.access.queries
Hank
external usenet poster
 
Posts: 51
Default 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  
Old May 21st, 2010, 03:01 PM posted to microsoft.public.access.queries
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.